looping through controls on userform

[charly1]

Hi All

I have a Userform which contains among other controls: 6 combo boxes, 4 multi select list boxes and 4 textboxes. They all have different names [not just listbox1 ,2 etc.].

The Userform is to enable data entry into an excel table. When i press the command button to add the data to the table I would like vba to run through all the above stated controls to check if any of them are empty, [in the case of the list boxes the it would need to check whether or not at least one selection has been made].

If any of the controls would actually be empty i would like a message box popup which lists which controls are empty and askes the user whether they still wish to proceed or not.

If anyone out there has the time to help me, or at least offer guidance It would be greatly appreciated.

 

[DanteAmor]

Try the following on your commandbutton:

Private Sub CommandButton1_Click()
  Dim ctrl As MSForms.Control
  Dim sMsg As String
  Dim rsp As VbMsgBoxResult
  Dim i As Long
  Dim atLeastOne As Boolean
 
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        If ctrl.Value = "" Then
          sMsg = sMsg & ctrl.Name & ", Textbox is Empty" & vbCr
        End If
      Case "ComboBox"
        If ctrl.ListIndex = -1 Then
          sMsg = sMsg & ctrl.Name & ", ComboBox Without a correct selection" & vbCr
        End If
      Case "ListBox"
        atLeastOne = False
        For i = 0 To ctrl.ListCount - 1
          If ctrl.Selected(i) Then
            atLeastOne = True
            Exit For
          End If
        Next
        If atLeastOne = False Then
          sMsg = sMsg & ctrl.Name & ", ListBox does not have at least one selected" & vbCr
        End If
    End Select
  Next
 
  If sMsg <> "" Then
    rsp = MsgBox("There are controls with problems, do you still wish to proceed?" & vbCr & sMsg, vbYesNo + vbQuestion)
    If rsp = vbNo Then
      Exit Sub
    End If
  End If

'
'Here continues your code
'
 
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[Herakles]

A

Try the following on your commandbutton:

Private Sub CommandButton1_Click()
  Dim ctrl As MSForms.Control
  Dim sMsg As String
  Dim rsp As VbMsgBoxResult
  Dim i As Long
  Dim atLeastOne As Boolean
 
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        If ctrl.Value = "" Then
          sMsg = sMsg & ctrl.Name & ", Textbox is Empty" & vbCr
        End If
      Case "ComboBox"
        If ctrl.ListIndex = -1 Then
          sMsg = sMsg & ctrl.Name & ", ComboBox Without a correct selection" & vbCr
        End If
      Case "ListBox"
        atLeastOne = False
        For i = 0 To ctrl.ListCount - 1
          If ctrl.Selected(i) Then
            atLeastOne = True
          End If
        Next
        If atLeastOne = False Then
          sMsg = sMsg & ctrl.Name & ", ListBox does not have at least one selected" & vbCr
        End If
    End Select
  Next
 
  If sMsg <> "" Then
    rsp = MsgBox("There are controls with problems, do you still wish to proceed?" & vbCr & sMsg, vbYesNo + vbQuestion)
    If rsp = vbNo Then
      Exit Sub
    End If
  End If

'
'Here continues your code
'
 
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

And if you want the message to be a bit more user friendly, put some text to use instead of the control name and control type in the control tag property and refer to it instead of the control name.

Use ctrl.Tag instead of ctrl.Name.

 

[charly1]

UNBELIEVABLE - THANKS A MILLION

is this code easy to adapt to only include the controls that are visible on the form, as depending on selections made by a user not all controls are made visible and need to be filled in?

 

[DanteAmor]

Try:

Private Sub CommandButton1_Click()
  Dim ctrl As MSForms.Control
  Dim sMsg As String
  Dim rsp As VbMsgBoxResult
  Dim i As Long
  Dim atLeastOne As Boolean
  
  For Each ctrl In Me.Controls
    If ctrl.Visible = True Then
      Select Case TypeName(ctrl)
        Case "TextBox"
          If ctrl.Value = "" Then
            sMsg = sMsg & ctrl.Name & ", Textbox is Empty" & vbCr
          End If
        Case "ComboBox"
          If ctrl.ListIndex = -1 Then
            sMsg = sMsg & ctrl.Name & ", ComboBox Without a correct selection" & vbCr
          End If
        Case "ListBox"
          atLeastOne = False
          For i = 0 To ctrl.ListCount - 1
            If ctrl.Selected(i) Then
              atLeastOne = True
              Exit For
            End If
          Next
          If atLeastOne = False Then
            sMsg = sMsg & ctrl.Name & ", ListBox does not have at least one selected" & vbCr
          End If
      End Select
    End If
  Next
  
  If sMsg <> "" Then
    rsp = MsgBox("There are controls with problems, do you still wish to proceed?" & vbCr & sMsg, vbYesNo + vbQuestion)
    If rsp = vbNo Then
      Exit Sub
    End If
  End If
  
End Sub

 

[charly1]

Thanks a million it works beautifully!