Runtime Error 424 Object Required (Add Data in Data Entry Userform)

I'm new to VBA and did not have any background at all. I just watched youtube videos and try to learn. I'm making a data entry for shipment summary information. However, I experienced a Runtime Error 464 when I run my Userform. I've checked my codes over and over and couldn't figure out the solution. Thanks in advance to those who'd help!

Private Sub CommandButton1_Click()
    Dim TextBox As String
    Dim Database As Worksheet
    Set Database = Worksheets("ShipData")

    eRow = ThisWorksheet.ShipData.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ShipData.Cells(eRow, 1).Value = TextBox6.Text
    ShipData.Cells(eRow, 1).Value = ComboBox1.List
    ShipData.Cells(eRow, 2).Value = ComboBox2.List
    ShipData.Cells(eRow, 3).Value = TextBox1.Text
    ShipData.Cells(eRow, 4).Value = TextBox2.Text
    ShipData.Cells(eRow, 5).Value = TextBox3.Text
    ShipData.Cells(eRow, 6).Value = ComboBox3.List
    ShipData.Cells(eRow, 7).Value = TextBox4.Text
    ShipData.Cells(eRow, 8).Value = TextBox5.Text
    ShipData.Cells(eRow, 9).Value = ComboBox4.Text
    ShipData.Cells(eRow, 11).Value = TextBox7.Text
    ShipData.Cells(eRow, 12).Value = TextBox8.Text
    ShipData.Cells(eRow, 13).Value = TextBox9.Text
    ShipData.Cells(eRow, 14).Value = TextBox10.Text
    ShipData.Cells(eRow, 15).Value = TextBox11.Text
    ShipData.Cells(eRow, 16).Value = TextBox12.Text
    ShipData.Cells(eRow, 17).Value = TextBox13.Text
    ShipData.Cells(eRow, 18).Value = TextBox14.Text
    ShipData.Cells(eRow, 19).Value = TextBox15.Text
    ShipData.Cells(eRow, 20).Value = TextBox16.Text

    Unload Me
    ShipmentSummary.Show
End Sub

1 answer

  • answered 2017-11-23 07:35 Shai Rado

    You already Set your "ShipData" worksheet with Set Database = Worksheets("ShipData"), so you can now reference it with Database, and not ThisWorksheet.ShipData.

    I believe this is what you are trying to achieve:

    Private Sub CommandButton1_Click()
    
    Dim TextBox As String
    Dim Database As Worksheet
    
    Set Database = Worksheets("ShipData")
    
    With Database
        eRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
        .Cells(eRow, 1).Value = TextBox6.Text
        .Cells(eRow, 1).Value = ComboBox1.List  '<-- better use ComboBox1.Value
        .Cells(eRow, 2).Value = ComboBox2.List  '<-- better use ComboBox2.Value
        .Cells(eRow, 3).Value = TextBox1.Text
        .Cells(eRow, 4).Value = TextBox2.Text
        .Cells(eRow, 5).Value = TextBox3.Text
        .Cells(eRow, 6).Value = ComboBox3.List  '<-- better use ComboBox3.Value
        .Cells(eRow, 7).Value = TextBox4.Text
        .Cells(eRow, 8).Value = TextBox5.Text
        .Cells(eRow, 9).Value = ComboBox4.Text  '<-- better use ComboBox4.Value
        .Cells(eRow, 11).Value = TextBox7.Text
        .Cells(eRow, 12).Value = TextBox8.Text
        .Cells(eRow, 13).Value = TextBox9.Text
        .Cells(eRow, 14).Value = TextBox10.Text
        .Cells(eRow, 15).Value = TextBox11.Text
        .Cells(eRow, 16).Value = TextBox12.Text
        .Cells(eRow, 17).Value = TextBox13.Text
        .Cells(eRow, 18).Value = TextBox14.Text
        .Cells(eRow, 19).Value = TextBox15.Text
        .Cells(eRow, 20).Value = TextBox16.Text
    End With
    
    Unload Me
    ShipmentSummary.Show
    
    End Sub