Excel restarts after running commandbutton

[vbahenk]

Hallo there,

In my form in vba there is a command button. If I try to use this to add an extra row Excel restarts.
The problem is in the .rowsource in the sub Refresh_data

I hope you can help me

Option Explicit
---------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")
Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

'========== Validations ==========
If Me.TextBox1.Value = "" Then
MsgBox "Please enter New/ Number", vbCritical
Exit Sub
End If
If Me.TextBox2.Value = "" Then
MsgBox "Please enter MAWB", vbCritical
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "Please enter Shipment/ Invoice", vbCritical
Exit Sub
End If
If Me.ComboBox1_Forwarder.Value = "" Then
MsgBox "Please enter Forwarder", vbCritical
Exit Sub
End If
If Me.ComboBox2_Carrier.Value = "" Then
MsgBox "Please enter Carrier/ Service", vbCritical
Exit Sub
End If
If Me.ComboBox3_Shipper.Value = "" Then
MsgBox "Please enter Shipper", vbCritical
Exit Sub
End If
If Me.ComboBox4_Code.Value = "" Then
MsgBox "Please enter Code", vbCritical
Exit Sub
End If
If Me.ComboBox5_Flow.Value = "" Then
MsgBox "Please enter Flow", vbCritical
Exit Sub
End If
'=================================

sh.Range("A" & Last_Row + 1).Value = Me.TextBox1.Value
sh.Range("B" & Last_Row + 1).Value = Me.TextBox2.Value
sh.Range("C" & Last_Row + 1).Value = Me.TextBox3.Value
sh.Range("D" & Last_Row + 1).Value = Me.TextBox4.Value
sh.Range("E" & Last_Row + 1).Value = Me.ComboBox1_Forwarder.Value
sh.Range("F" & Last_Row + 1).Value = Me.ComboBox2_Carrier.Value
sh.Range("G" & Last_Row + 1).Value = Me.ComboBox3_Shipper.Value
sh.Range("H" & Last_Row + 1).Value = Me.ComboBox4_Code.Value
sh.Range("I" & Last_Row + 1).Value = Me.ComboBox5_Flow.Value

If OptionButton1.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 10).Value = "Direct"
ElseIf OptionButton2.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 10).Value = "Broker"
End If

If OptionButton3.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 11).Value = "Yes"
ElseIf OptionButton4.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 11).Value = "No"
End If

If OptionButton5.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 12).Value = "Yes"
ElseIf OptionButton6.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 12).Value = "No"
End If

If OptionButton7.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 13).Value = "Yes"
ElseIf OptionButton8.Value = True Then
Worksheets("Database").Cells(Last_Row + 1, 13).Value = "No"
End If

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox1_Forwarder.Value = ""
Me.ComboBox2_Carrier.Value = ""
Me.ComboBox3_Shipper.Value = ""
Me.ComboBox4_Code.Value = ""
Me.ComboBox5_Flow.Value = ""
Me.OptionButton1.Value = ""
Me.OptionButton2.Value = ""
Me.OptionButton3.Value = ""
Me.OptionButton4.Value = ""
Me.OptionButton4.Value = ""
Me.OptionButton6.Value = ""
Me.OptionButton7.Value = ""
Me.OptionButton8.Value = ""

Call Refresh_data
End Sub

---------------------------------------------------------------------------------------------------------------------------

Sub Refresh_data()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")
Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 13
.ColumnWidths = "60,70,70,65,60,55,55,60,60,55,55,55,55"
.RowSource = "Database!A2:M" & Last_Row
End With

Me.ListBox1.TopIndex = ListBox1.ListCount - 1
End Sub

 

[vbahenk]

I tried .RowSource = "Database!A2:M250" and that worked fine. So there is something with finding the last row.

 

[vbahenk]