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.