Automation error


Board Regular
Jul 15, 2016
Hi i have the following error -2147418104 (80010008) automation error - the object invoked has disconnected from its clients ,but this error occurs very rarely after 70-100 data entries for few people and rest they don't encounter this problem, does anyone have solution for this ? please help out

Private Sub CommandButton1_Click()
Dim lrow As Long
Dim x As Long
Dim y As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

If Len(TextBox4.Text) = 0 Then
MsgBox "oops please enter the SSID ", vbCritical
Exit Sub
End If

If Len(ComboBox1.Text) = 0 Then
MsgBox "oops please enter the PSET ", vbCritical
Exit Sub
End If

If Len(ComboBox2.Text) = 0 Then
MsgBox "oops please enter the COUNTRY NAME ", vbCritical
Exit Sub
End If

If Len(ComboBox3.Text) = 0 Then
MsgBox "oops please enter the ROLL UP CODE", vbCritical
Exit Sub
End If

lrow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lrow, 2).Value = TextBox1.Value
.Cells(lrow, 3).Value = TextBox2.Value
.Cells(lrow, 5).Value = TextBox3.Value
.Cells(lrow, 8).Value = TextBox4.Value

.Cells(lrow, 14).Value = TextBox7.Value
.Cells(lrow, 11).Value = TextBox8.Value
.Cells(lrow, 13).Value = TextBox9.Value
.Cells(lrow, 9).Value = ComboBox1.Value
.Cells(lrow, 10).Value = ComboBox2.Value
.Cells(lrow, 12).Value = ComboBox3.Value

If OptionButton1 = True Then
.Cells(lrow, 4).Value = "NEW"
ElseIf OptionButton2 = True Then
.Cells(lrow, 4).Value = "MODIFY"
ElseIf OptionButton3 = True Then
.Cells(lrow, 4).Value = "NAR"
ElseIf OptionButton4 = True Then
.Cells(lrow, 4).Value = "REX"
End If

If OptionButton5 = True Then
.Cells(lrow, 6).Value = "YES"
ElseIf OptionButton6 = True Then
.Cells(lrow, 6).Value = "NO"
End If

If OptionButton7 = True Then
.Cells(lrow, 11).Value = "EQ/EQTY/ALL"
ElseIf OptionButton8 = True Then
.Cells(lrow, 11).Value = "FI/ALL/ALL"
ElseIf OptionButton9 = True Then
.Cells(lrow, 11).Value = "FI/CORP/ALL"
ElseIf OptionButton10 = True Then
.Cells(lrow, 11).Value = "FI/GOVT/ALL"
End If

If OptionButton11 = True Then
.Cells(lrow, 7).Value = "3 TO 10"
ElseIf OptionButton12 = True Then
.Cells(lrow, 7).Value = "LESS THAN 3"
ElseIf OptionButton13 = True Then
.Cells(lrow, 7).Value = "MORE THAN 10"
End If

End With

Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
Dim rngBlank As Range: Set rngBlank = rngUR.Find("")

While Not rngBlank Is Nothing
rngBlank.Value = rngBlank.Offset(-1, 0).Value
Set rngBlank = rngUR.Find("", rngBlank)

Dim ctrl
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.TextBox Then
ctrl.Text = ""
End If
If TypeOf ctrl Is msforms.OptionButton Then
ctrl.Value = False
End If

If TypeOf ctrl Is msforms.ComboBox Then
ctrl.Value = ""
End If
Next ctrl

End Sub

Private Sub CommandButton2_Click()

Unload Newalert

End Sub

Private Sub TextBox1_Change()
TextBox1.Value = UCase(TextBox1.Value)
End Sub

Private Sub TextBox2_Change()
TextBox2.Value = UCase(TextBox2.Value)
End Sub

Private Sub TextBox3_Change()
TextBox3.Value = UCase(TextBox3.Value)
End Sub

Private Sub TextBox4_Change()
TextBox4.Value = UCase(TextBox4.Value)
End Sub

Private Sub TextBox5_Change()
TextBox5.Value = UCase(TextBox5.Value)
End Sub

Private Sub TextBox6_Change()
TextBox6.Value = UCase(TextBox6.Value)
End Sub

Private Sub TextBox7_Change()
TextBox7.Value = UCase(TextBox7.Value)
End Sub

Private Sub TextBox8_Change()
TextBox8.Value = UCase(TextBox8.Value)
End Sub

Private Sub TextBox9_Change()
TextBox9.Value = UCase(TextBox9.Value)
End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...