Loop through UserForm Controls and perform Different actions depending on ...

[melpa]

I have a UserForm with several TextBoxes and ComboBoxes.

With CommandButton1 I want to clear them all.
With CommandButton2 I want to change the BackColor of the empty ones.
With CommandButton3 I want to restore any font colour back if it has been modified.

I can do this using the code below, however I would like to know how to write the the loop routine just once and somehow add an instrucion to each command button to determine which action is applied.

I have not been able to find the answer with a Google search and I lost the records of my failed attempts over the last couple of hours.
I tried nested ifs, but it got very messy and complicated which defeats the purpose of learning to code concisely. Also, it didn't work.

I trust there's a neat way of doing this and I'm hoping there's an expert available who can help me learn how to do it.

Sub UserFormLoop_MultipleControls()
'PURPSOSE: Loop through multiple types of controls on your VBA UserForm
'www.TheSpreadsheetGuru.com/the-code-vault

Dim ctrl As Control
Dim ctrlType1 As String
Dim ctrlType2 As String

'What control type to loop through
  ctrlType1 = "CheckBox"
  ctrlType2 = "TextBox"

'Loop Through each control on UserForm
  For Each ctrl In UserForm1.Controls
    'Narrow down to specific type
      If TypeName(ctrl) = ctrlType1 Or TypeName(ctrl) = ctrlType2 Then
        
        'Do Something With That Control Type...
  
          '''INSTRUCTION GOES HERE ie. change Value  / change BackColor / change ForeColor
          
      End If
  Next ctrl

End Sub

[snb]

The neatest way is to provide us with a sample representative workbook.

[Paul_Hossler]

I don't think that just checking the Type is enough; you could have 2 Command Buttons that do different things

I think that .Name or .Tag would be more reliable

Option Explicit


Private Sub CommandButton3_Click()
    Dim oCtrl As Control




    For Each oCtrl In Me.Controls
    
        With oCtrl
    
            Select Case .Name
                Case "CheckBox1"
                    MsgBox .Name & " = " & .Value
                Case "TextBox1"
                    MsgBox .Name & " = " & .Text
                Case "OptionButton1", "OptionButton2"
                    MsgBox .Name & " = " & .Value
            End Select
        End With
    Next


End Sub

[snb]

The opposite is true: checking for typename is the most reliable. A name can be altered, the tag too, but the typename is constant.

[Paul_Hossler]

The opposite is true: checking for typename is the most reliable. A name can be altered, the tag too, but the typename is constant.

you could have 2 Command Buttons that do different things

If CommandButton1 makes the ActiveCell Red and if CommandButton2 makes the ActiveCell Blue, how would just checking TypeName determine what color to make the ActiveCell?

[melpa]

Below is what I have. I want to know how I could achieve this writing the UFLoop_Controls sub just once.

This is on the userform

Option Explicit

Sub UFLoop_Controls1()

Dim ctrl As Control

    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            ctrl.Text = ""
        End If
    Next ctrl
    
End Sub

Sub UFLoop_Controls2()

Dim ctrl As Control

     For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            If ctrl.Text = "" Then
                ctrl.BackColor = rgbPink
            End If
        End If
    Next ctrl

End Sub

Sub UFLoop_Controls3()

Dim ctrl As Control

    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            ctrl.ForeColor = UserForm1.ForeColor
        End If
    Next ctrl

End Sub

This is in a module

Option Explicit

Private Sub CommandButton1_Click()
    UFLoop_Controls1
End Sub

Private Sub CommandButton2_Click()
    UFLoop_Controls2
End Sub

Private Sub CommandButton3_Click()
    UFLoop_Controls3
End Sub

End Sub

[melpa]

Thanks for the workbook, Paul_Hossler, I'll have a look.

I don't understand what the Tag property is or how to use it. If you have a simple way to explain it, I'd appreciate it.

[Paul_Hossler]

One way (using TypeName)

Standard module:

Option Explicit


Sub UFLoop_Controls(X As Long)
    Dim ctrl As Control


    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            Select Case X
                Case 1
                    ctrl.Text = ""
                Case 2
                    If ctrl.Text = "" Then ctrl.BackColor = rgbPink
                Case 3
                    ctrl.ForeColor = UserForm1.ForeColor
            End Select
        End If
    Next ctrl
    
End Sub

UF module:

Option Explicit


Private Sub CommandButton1_Click()
    Call UFLoop_Controls(1)
End Sub


Private Sub CommandButton2_Click()
    Call UFLoop_Controls(2)
End Sub


Private Sub CommandButton3_Click()
    Call UFLoop_Controls(3)
End Sub

[melpa]

Thank you!! I've yet to try it out, but that's exactly what I mean. So beautifully simple.

[melpa]

Works perfectly! Thanks again.