vba - 需要运行时错误 424 对象(在数据输入用户窗体中添加数据)

Question

我是 VBA 新手，根本没有任何背景。我只是看了 youtube 视频并尝试学习。我正在为货件摘要信息输入数据。但是，我遇到了 Runtime Error 464当我运行我的用户窗体时。我一遍又一遍地检查我的代码，但无法找出解决方案。提前感谢那些提供帮助的人!

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

Answer 1

你已经Set您的 "ShipData"带有 Set Database = Worksheets("ShipData") 的工作表，因此您现在可以使用 Database 引用它, 而不是 ThisWorksheet.ShipData .

我相信这就是您要实现的目标:

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