Excel VBA 用户窗体标签属性

Question

我有一个带有文本和组合框的用户表单，其中一些代表“必填字段”并且不能留空。我已为目标控件的标记属性输入值 RQD。我的目标是循环遍历控件并使用它们的 Tag 属性来识别不能为空的控件（其中 Tag 属性值 = RQD），并更改它们的 BackColor 属性（如果为空）。但是，我无法得到这项工作。以下是一些代码：-

With frm_RecCapture
    '
    .lbl01_RecDate = Format(Date, "Long Date", vbSunday)
    .txt01_RecNum = Format(RecNum, "000000")
    .txt01_RecNum.Enabled = False
    .txt01_AccNum.SetFocus
    '
    frmComplete = False
    .Show
    '
    Do While frmComplete = False
        .Show
        '
        For Each frmCtrl In .Controls
            If TypeName(frmCtrl) = "Textbox" Or TypeName(frmCtrl) = "Combobox" Then
                If frmCtrl.Tag = "RQD" And frmCtrl.Text = "" Then
                    frmCtrl.BackColor = &HFFFF&
                    n = n + 1
                End If
            End If
        Next frmCtrl
        '
        If n = 0 Then
            frmComplete = True
        Else
            frmComplete = False
            MsgBox "ERROR! Fields highlighted in yellow cannot be left blank. Please "
            complete these fields before continuing.", vbInformation + vbOKOnly, SysTitle
        End If
    Loop
    '
End With

有什么建议吗？谢谢...

Answer 1

我更喜欢编写一个包装和验证控件的类。您需要将对包装类的引用存储在某种类型的类级别变量的集合中，以防止其超出范围。

类：RequiredFieldControl

Option Explicit
Private WithEvents ComboBox  As MSForms.ComboBox
Private WithEvents TextBox  As MSForms.TextBox
Private mControl  As MSForms.Control

Private Const DefaultBackColor As Long = -2147483643
Private Const InvalidBackColor As Long = vbYellow ' &HFFFF&

Public Property Set Control(ByVal Value As MSForms.Control)
    Set mControl = Value
    Select Case TypeName(Control)
        Case "ComboBox"
            Set ComboBox = Value
        Case "TextBox"
            Set TextBox = Value
    End Select
    FormatControl
End Property

Public Property Get Control() As MSForms.Control
    Set Control = mControl
End Property

Sub FormatControl()
    Control.BackColor = IIf(isValid, DefaultBackColor, InvalidBackColor)
End Sub

Public Function isValid() As Boolean
    Select Case TypeName(Control)
        Case "ComboBox"
            isValid = ComboBox.ListIndex > -1
        Case "TextBox"
            isValid = Len(TextBox.Value) > 0
    End Select
End Function

Private Sub ComboBox_Change()
    FormatControl
End Sub

Private Sub TextBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    FormatControl
End Sub

表单代码

Private RequiredFields As Collection

Private Sub UserForm_Initialize()
    Set RequiredFields = New Collection
    AddRequiredFields Me.Controls
    ComboBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    Debug.Print RequiredFields.Count
End Sub

Sub AddRequiredFields(pControls As MSForms.Controls)
    Dim RequiredField As RequiredFieldControl
    Dim Control As MSForms.Control
    For Each Control In pControls
        If Control.Tag = "RQD" Then
            Select Case TypeName(Control)
                Case "ComboBox", "TextBox"
                    Set RequiredField = New RequiredFieldControl
                    Set RequiredField.Control = Control
                    RequiredFields.Add RequiredField
            End Select
        Else
            On Error Resume Next
            AddRequiredFields Control.Controls
            On Error GoTo 0
        End If
       
    Next
End Sub

Function AreAllRequiredFieldsFilled() As Boolean
    Dim RequiredField As RequiredFieldControl
    For Each RequiredField In RequiredFields
        If Not RequiredField.isValid Then Exit Function
    Next
    AreAllRequiredFieldsFilled = True
End Function