用户窗体运行时错误"424"：对象需要 Excel VBA

Question

我有一个用户表单，然后单击一个按钮，它应该访问或打开用户表单。但是每次代码到达那部分，

运行时错误'424'： 需要对象

弹出。这是我的代码：

    If CheckSheet(TextBoxValue) = True Then
        Sheets(TextBoxValue).Select
        UserForm.Show
    Else
        Set Worksheet = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
        Worksheet.Name = TextBoxValue
        Dim label As Control
        For Each label In UserForm.Controls
            If TypeName(label) = "Label" Then
                With ActiveSheet
                    i = i + 1
                    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(lastRow, i).Value = label.Caption
                End With
            End If
        Next
        UserForm.Show
    End If

每次使用UserForm.Show和For Each label In UserForm.Controls

到达零件

我已经多次检查了表单的拼写，并且非常相同。

Answer 1

您可能已经想到了类似的东西： -

Sub TestCode()
    Dim Ws As Worksheet                 ' "Worksheet" is a reserved word
    Dim MyForm As UserForm1             ' "UserForm" is a reserved word
    Dim MyLabel As Control              ' "Label" is a reserved word
    Dim C As Long                       ' better name for a column than "i"
    Set MyForm = New UserForm1
    If GetSheet(Ws) Then
        For Each MyLabel In MyForm.Controls
            If TypeName(MyLabel) = "Label" Then
                With Ws                 ' true, Ws is the ActiveSheet but
                                        ' always use the same name for the same sheet
                    C = C + 1
                    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                    .Cells(LastRow, C).Value = MyLabel.Caption
                End With
            End If
        Next
    End If
    MyForm.Show
End Sub
Private Function GetSheet(Ws As Worksheet) As Boolean
    ' return True if Ws didn't exist
    Dim Ws As Worksheet
    On Error Resume Next
    Set Ws = Worksheets(TextBoxValue)
    If Err Then                     ' Err = doesn't exist
        Set Ws = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
        Ws.Name = TextBoxValue
        GetSheet = True
    End If
End Function
Private Function TextBoxValue() As String
    TextBoxValue = "MySheetName"
End Function

为了测试单词是否为"保留"单词，请在VB编辑器中选择它，然后按F1。如果MS Office使用它，请不要争论。