melpa
06-08-2020, 03:31 AM
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
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