excel - 如何显示一个空控件所在页面的 msgbox？

Question

我有在用户窗体中显示每个空控件及其所在页面的代码（我还突出显示了该字段）。如果用户忘记了大量控件，则 msgbox 会因为固定长度而中断。如果上面有一个空字段，有没有办法让这个代码只声明页面名称（一次）？

例子：需要转这个：

进入这个：

这是我用来收集所有感兴趣的控件名称的代码。

Private Sub CommandButton1_Click()
    Dim colBlankFields As New Collection, colPageNames As New Collection
    Dim oneControl As MSForms.Control, onePage As MSForms.Page
    Dim i As Long
    Dim strPrompt As String

    For i = 0 To Me.MultiPage1.Pages.Count - 1
        'if statement to check if page already in collection
            colPageNames.Add item:=onePage, key:=oneControl
            For Each oneControl In ControlsOfInterest
                oneControl.BackColor = vbWhite
                If oneControl.Name Like "opt*" Then
                    If Not OptionGroupSelectionMade(oneControl) Then
                        oneControl.BackColor = RGB(255, 128, 128)
                        colBlankFields.Add item:=oneControl, key:=oneControl.Name
                        strPrompt = strPrompt & vbCr & oneControl.Name & " on page " & oneControl.Parent.Caption
                     End If
                Else
                If oneControl.Visible And oneControl.Text = vbNullString Then
                        oneControl.BackColor = RGB(255, 128, 128)
                        colBlankFields.Add item:=oneControl, key:=oneControl.Name
                        strPrompt = strPrompt & vbCr & oneControl.Name & " on page " & oneControl.Parent.Caption
                    End If
                End If
            Next oneControl
        End If
    Next i
    If colBlankFields.Count <> 0 Then
        MsgBox strPrompt & vbCr & oneControl.Name & " on page " & oneControl.Parent.Caption
    End If
End Sub

Function OptionGroupSelectionMade(oneButton As MSForms.OptionButton) As Boolean
    Dim oneControl As MSForms.Control

    For Each oneControl In Me.Controls
        If TypeName(oneControl) = "OptionButton" Then
            If oneControl.Parent.Name = oneButton.Parent.Name Then
                If oneButton.GroupName = oneControl.GroupName Then
                    If oneControl.Value Then
                        OptionGroupSelectionMade = True
                        Exit For
                    End If
                End If
            End If
        End If
    Next oneControl
End Function

初始化事件：

Private Sub UserForm_Initialize()
    Dim oneControl As MSForms.Control
    Dim onePage As MSForms.Page
    ' existing code

    Set ControlsOfInterest = New Collection
    For Each onePage In Me.MultiPage1.Pages
        For Each oneControl In onePage.Controls

            If oneControl.Name Like "txt*" And oneControl.Visible Then
                ControlsOfInterest.Add item:=oneControl, key:=oneControl.Name
            ElseIf oneControl.Name Like "opt*" And oneControl.Visible Then
                ControlsOfInterest.Add item:=oneControl, key:=oneControl.Name
            ElseIf oneControl.Name Like "cmb*" And oneControl.Visible Then
                ControlsOfInterest.Add item:=oneControl, key:=oneControl.Name
            End If

        Next oneControl
    Next onePage
End Sub

标签： excelvba

Answer 1

您可以通过多种方式执行此操作。

第一：将您的控件分成页面。然后遍历页面，检查该页面上的控件。当您找到一个空控件时，将该页面添加到列表中并停止检查该页面上的其余控件。

第二：与第一个类似，每次你找到一个空白的控件时，如果页面不在列表中，则将页面添加到列表中。如果页面已经在列表中，则跳过它。

向用户显示页面列表。

这是基于您更新的代码的一些代码。

除非您按页面在集合中列出了控件，否则我不会遍历页面。VBA 不允许多 for 循环在退出时中断，这很烦人。

这是概念代码：

Private Sub CommandButton1_Click()
    Dim colBlankFields As New Collection, colPageNames(1) As String
    Dim oneControl As MSForms.Control, onePage As MSForms.Page
    Dim i As Long
    Dim strPrompt As String
    
    colPageNames(0)="No Match"

      For Each oneControl In ControlsOfInterest
          oneControl.BackColor = vbWhite
          If oneControl.Name Like "opt*" Then
              If Not OptionGroupSelectionMade(oneControl) Then
                  'Check here if we have already found a problem on this Page
                  If (UBound(VBA.Filter(colPageNames,oneControl.Parent.Caption))<0) Then 'if page is not in list already
                      oneControl.BackColor = RGB(255, 128, 128)
                      colBlankFields.Add item:=oneControl, key:=oneControl.Name
                      strPrompt = strPrompt & vbCr & oneControl.Name & " on page " & oneControl.Parent.Caption
                      colPageNames(Ubound(colPageNames))=oneControl.Parent.Caption 'Add page to array list
                  End If
               End If
          Else
          If oneControl.Visible And oneControl.Text = vbNullString Then
                 If (UBound(VBA.Filter(colPageNames,oneControl.Parent.Caption))<0) Then 'if page is not in list already                  
                    oneControl.BackColor = RGB(255, 128, 128)
                    colBlankFields.Add item:=oneControl, key:=oneControl.Name
                    strPrompt = strPrompt & vbCr & oneControl.Name & " on page " & oneControl.Parent.Caption
                    colPageNames(Ubound(colPageNames))=oneControl.Parent.Caption 'Add page to array list
                 End If  
              End If
          End If
          Next oneControl
    
    If colBlankFields.Count <> 0 Then
        MsgBox strPrompt & vbCr & oneControl.Name & " on page " & oneControl.Parent.Caption
    End If
End Sub

Function OptionGroupSelectionMade(oneButton As MSForms.OptionButton) As Boolean
    Dim oneControl As MSForms.Control

    For Each oneControl In Me.Controls
        If TypeName(oneControl) = "OptionButton" Then
            If oneControl.Parent.Name = oneButton.Parent.Name Then
                If oneButton.GroupName = oneControl.GroupName Then
                    If oneControl.Value Then
                        OptionGroupSelectionMade = True
                        Exit For
                    End If
                End If
            End If
        End If
    Next oneControl
End Function