Excel crashes when I try to assign the value of a TextBox to a cell.

Question

Good evening Microsoft Community,

This is my first time writing here, but I don't know what else to do. I`m trying to perform what should be a routine procedure but this time, Excel throws me an error, telling me to debug it but then it closes.

I have a Form that is used to add information to a Table located in the Zona_Comis Tab (this is the CodeName of the Tab), the Form has a TextBox (Tbox_Comis), and three ComboBoxes (Tbox_Comun, Tbox_Prov and TBox_Estan). A procedure fills each ComboBox with a global variable I have in the Form Module. The thing is that when I execute the code that I have in the Add Button, it always fails at the same line of code and throws me an error, and then Excel crashes.

---

This Is the code and the line that is giving me the error:

Private Sub AComis_Add_Click() 
'BOTON AGREGAR COMISIONISTA 
Dim ult_fila As Integer 
Dim celda As Range 

    If Zona_Comis.Range("B2").Value = vbNullString Then 
        ult_fila = Zona_Comis.Range("A" & Rows.Count).End(xlUp).Row 
    Else 
        ult_fila = Zona_Comis.Range("A" & Rows.Count).End(xlUp).Row + 1 
    End If 
  
    Set celda = Zona_Comis.Range("A" & ult_fila) 

    If TBox_Comis.Value = "" Or TBox_Comun.Value = "" Or TBox_Prov.Value = "" Or TBox_Estan.Value = "" Or 		TBox_PDistrib.Value = "" Then 
        MsgBox prompt:="Por favor rellene todos los campos.", Buttons:=vbOKOnly, Title:="CFS - Agregar Comisionsita" 
    Else 
        celda.Value = TBox_Comis.Value   ==============> THIS IS THE LINE THAT'S ALWAYS CAUSING THE ERROR(*)
        celda.Offset(0, 1).Value = TBox_Comun.Value 
        celda.Offset(0, 2).Value = TBox_Prov.Value 
        celda.Offset(0, 3).Value = TBox_Estan.Value 
        celda.Offset(0, 4).Value = TBox_PDistrib.Value 

        MsgBox prompt:="Los datos se ha agregado correctamente", Buttons:=vbOKOnly, Title:="CFS - Agregar Comisionsita" 

         Add_Comis.Hide 
         Menu_Comis.Show 
    End If 
 End Sub

(*) Usually, when I get the error and open the debugger, I can see that the cell has indeed taken the value of the ComboBox TBox_Comis, but when I press F8 to run the line, Excel closes.

---

The code has given me several different errors, I describe them here:

1. Run-time error '-2147417848 (800 10 108)' occurred: Error in method 'Value' of object 'Range' This is the most common error that I get.

1. Runtime error '-2147417848 (800 10 108)' occurred: Automation Error. Lost connection to type library or object library for remote processes. Click 'OK' to Remove the reference. I have only had this error twice. It says that I have to click the 'OK' button but I only got the buttons: 'Continue' (which is disabled), 'Finish', 'Debug' and 'Help'. On the first occasion, I didn't have time to click on anything because Excel closed, and on the second time, I clicked on Debug, and then Excel closed.

I have tried to repair Microsoft Office but the problem persists. I have debugged step by step verifying all the procedures of the previous Forms but the problem is always that same line of code.

I must admit that I'm not an expert in programming in VBA, I'm probably doing something wrong and it's a silly mistake but I can't identify it. This error has taken me a lot of time and I don't know how to fix it.

Please, if someone could help me, I will be eternally grateful.

---

Information about my System:

• Processor: Intel(R) Core(TM) i7-5500U CPU @ 2.40GHz 2.40 GHz
• RAM: 16.0GB
• System Type: 64-bit operating system, x64-based processor
• Edition: Windows 10 Pro
• Microsoft Office Professional Plus 2019

---

Here is the link with a test file to check the error: https://docs.google.com/spreadsheets/d/1RnTERZGnTaF8Wq08PdTTzZxLzIx2m8-f/edit?usp=sharing&ouid=114028205103310400686&rtpof=true&sd=true

The steps to run the macro that's causing the problem are as follows:

1. Go to the T_ZONA_COMIS Tab and click on the "MENU COMISIONISTAS" button.
2. In the pop-up window, click on "Agregar Comisionista y Porcentaje de Distribuidora por Estanco"
3. In the next window, fill in the data (it can be anything)
4. Finally, click on the "Agregar" button ======> Then the error that I mentioned will be generated.

Thank you so very much everyone in advance :)

Answer 1

Hello Valentin, thank you for taking the time to respond :)

I changed the code replacing it with what you suggested but the same thing keeps happening.

It stops in the same line, and when I try to debug with F8, Excel closes.

The thing is, this problem only happens when I run the macro from the button "MENU COMISIONISTAS" and follow the steps to get to the Form Add_Comis (which has the "AComis_Add_Click" procedure). But when I run the macro directly from the "AComis_Add_Click" procedure, everything works fine.

UPDATE: I found a solution!!

I found a similar case in this forum:

https://www.excelforum.com/excel-programming-vba-macros/1131764-user-form-listbox-rowsource-problem-and-solution.html

Turns out the error was caused by another line of code in the previous Form.

I have a ListBox which I populate using the .RowSource method and it's linked to the table that was trying to add the information to, using the procedure mentioned above.

The thing is, I reset the value of the ListBox.RowSource to "" before adding new information to the Table via the AComis_Add_Click procedure and it worked!

I don't know why this happens (I'm really interested to know this), don't know if it's a bug in Excel or I`m just not using the .RowSource method right...

If anyone could explain this to me, I would really appreciate it

Hope this can help someone with a similar problem :)

Thanks, Microsoft Community, good night! :)