向现有VBA代码新增ComboBox7控制逻辑时出现object required错误

报错原因 #

你新增的逻辑中直接调用ComboBox7、TextBox37控件时未指定所属窗体对象：该函数通过入参Form接收当前操作的窗体实例，函数作用域内无法直接识别未绑定对象的控件名，因此抛出「需要对象」错误。

修复方法 #

将控件调用部分加上Form.前缀，明确所属窗体即可。如果需要将清空后的文本框值同步到后续写入工作表的RecordData数组，还需同步更新数组对应位置的内容，避免窗体显示清空但写入工作表还是旧值的问题。

修复后的完整代码如下：

Function IsComplete(ByVal Form As Object, ByVal sh As Object, ByRef RecordData() As Variant) As Boolean

    Dim i           As Integer, a As Integer, ArrayCol As Integer
    Dim msg         As String
    Dim ControlName As Variant
    Dim IsRequired  As Boolean, IsValidDataType As Boolean
    Dim HasEntry    As Boolean

    On Error GoTo myerror
    
    ControlName = GetControls(Form)
    
    ReDim RecordData(1 To 1, 1 To 43)
    
    For i = LBound(ControlName, 1) To UBound(ControlName, 1)
        With Form.Controls(ControlName(i, 1))
            IsRequired = UCase(ControlName(i, 2)) = "YES"
            HasEntry = Len(.Text) > 0
            ArrayCol = ControlName(i, 7) - 1
            If HasEntry Then
                'validate selected data entry type
                Select Case ControlName(i, 3)
                
                    Case "Numeric"
                        'numeric only
                        IsValidDataType = IsNumeric(.Text)
                        RecordData(1, ArrayCol) = Val(.Value)
                        'date
                    Case "Date"
                        IsValidDataType = IsDate(.Text)
                        If IsValidDataType Then RecordData(1, ArrayCol) = DateValue(.Value)
                        'text (default)
                    Case "Text", ""
                        IsValidDataType = Len(.Text) > 0
                        RecordData(1, ArrayCol) = .Value
                        If IsValidDataType Then
                            Select Case i
                                Case 1, 2, 5, 6, 8, 12
                                    RecordData(1, ArrayCol) = StrConv(RecordData(1, ArrayCol), vbProperCase)
                                Case 10
                                    RecordData(1, ArrayCol) = StrConv(RecordData(1, ArrayCol), vbUpperCase)
                                     Case 7
                                        RecordData(1, ArrayCol) = Environ("username")
                            End Select
                        End If
                        
                End Select
            
            End If
            
            If IsRequired And Not HasEntry Then
                msg = IIf(ControlName(i, 4) = 0, "Entry Required", ControlName(i, 4))
                MsgBox msg, 48, "Entry Required"
                .SetFocus
                Exit Function
            ElseIf HasEntry And Not IsValidDataType Then
                MsgBox "Invalid Data Type", 48, "Invalid Entry"
                .SetFocus: Exit Function
            Else
            End If
        End With
endloop:
    Next i
    
    ' 修复后的控件调用逻辑，删除无意义的空If分支
    If Form.ComboBox7.Value = "Yes" Then
        Form.TextBox37.Value = ""
        Form.TextBox38.Value = ""
        ' 按需开启下方代码，替换为你实际的列索引
        ' RecordData(1, TextBox37对应的列索引-1) = ""
        ' RecordData(1, TextBox38对应的列索引-1) = ""
    End If

    IsComplete = True
    
myerror:
    If Err <> 0 Then MsgBox (Error(Err))
End Function

内容的提问来源于stack exchange，提问作者Harin