Automation error

[dwarek]

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)
Wend


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

 

[dwarek]

at least does someone knows how to correct automation error?