对象Var或Block未设置VBA

Question

我试图写一个VBA代码复制活动行到一个新的工作表。然后拉出一个包含文本框条目和列表条目的用户表单。输入后，它将相应地更新单元格并更新日期。

我目前得到运行时错误'91':对象变量或块变量未设置。我不知道这个错误是从哪里来的，如果你能帮助并解释给我，我会非常感激!

有两个文件，一个调用用户表单，这是我得到错误的地方。

Sub NewCange()
 ChangeUserForm.Show

End Sub

和用户表单代码

Private Sub UserForm_Initialize()

    Dim i As Integer, M As Integer, Result As Integer
    Dim N As String
    Dim MasterRange As Range, CopyRange As Range, NextRow As Range
    Dim ActiveRow As Long


  'Selects active row copies it and puts in empty row in the copy sheet

     ActiveRow = ActiveCell.Row
            With Worksheets("Master")
                   .Activate
                   .Cells(ActiveRow, 1).Select
                   .Cells(ActiveRow, 1).EntireRow.Copy
            End With
            With Worksheets("Records")
                   .Activate
                   .Cells(ActiveRow, 1).Select
                Rows(ActiveRow).Select
        End With
         ActiveSheet.Paste      
Destination:=Worksheets("Records").Cells(ActiveRow, 1)

'Below here is the inserting new change into excel sheet


    Set MasterRange = Range(ActiveCell, ActiveCell)
     Worksheets("Master").Activate
    LocalListBox.Clear
'List of Locations for assets
With LocalListBox
    .AddItem "TB01 "
    .AddItem "TB02 "
    .AddItem "TB03 "
    .AddItem "TB04 "
    .AddItem "TB05 "
    .AddItem "TB06 "
    .AddItem "TB07 "
    .AddItem "TB07XP "
    .AddItem "TB08 "
    .AddItem "TB09 "
    .AddItem "TB10 "
    .AddItem "TB11 "
    .AddItem "TB12 "
    .AddItem "TB13 "
    .AddItem "TB14 "
    .AddItem "TB15 "
    .AddItem "TB16 "
    .AddItem "TB17 "
    .AddItem "TB17XP "
    .AddItem "TB18 "
    .AddItem "TB19 "
    .AddItem "TB20 "
    .AddItem "TB21 "
    .AddItem "TB23 "
    .AddItem "TB24 "
    .AddItem "TB25 "
    .AddItem "TB26 "
    .AddItem "TB27 "
    .AddItem "TB27XP "
    .AddItem "TB28 "
    .AddItem "TB29 "
    .AddItem "TB30 "
    .AddItem "TB31 "
    .AddItem "TB32 "
    .AddItem "CAB3 "
    .AddItem "CAB4 "
    .AddItem "CAB5 "
    .AddItem "CAB6 "
    .AddItem "CAB7 "
    .AddItem "CAB8 "
    .AddItem "CAB9 "
    .AddItem "CAB10 "
    .AddItem "CAB12 "
    .AddItem "CAB16 "
    .AddItem "CAB17 "
    .AddItem "CAB18 "
    .AddItem "CAB19 "
End With

   Set NameRange = ActiveCell
MasterRange = LocalListBox.Value
MasterRange.Offset(0, 2) = NameTextBox.Value
MasterRange.Offset(0, 1) = Date
Unload Me

End Sub

Answer 1

运行时错误'91':对象变量或块变量未设置。

你得到这个错误是因为你试图卸载正在初始化的东西。

所以基本上你不能这样做

Private Sub UserForm_Initialize()
    Unload Me
End Sub

移除Unload Me并将其放入按钮点击中。这样，您还可以让用户从列表框中选择一个值LocalListBox:)

将此代码块移动到Button中点击

Private Sub CommandButton1_Click()
    MasterRange = LocalListBox.Value
    MasterRange.Offset(0, 2) = NameTextBox.Value
    MasterRange.Offset(0, 1) = Date
    Unload Me
End Sub

和在用户表单的顶部声明Dim MasterRange As Range而不是在UserForm_Initialize()

Answer 2

看起来你在With语句中缺少一个句号:

With Worksheets("Records")
               .Activate
               .Cells(ActiveRow, 1).Select
            Rows(ActiveRow).Select
    End With

应该

   With Worksheets("Records")
           .Activate
           .Cells(ActiveRow, 1).Select
        .Rows(ActiveRow).Select
End With

另外，要准确地找到您的错误所在，请使用F8"遍历"宏。此外，强烈建议避免在VBA中使用.Select。