How should I Use 1 code for same Objects in VBA Excel

Question

Im working with a workbook with many sheets and im using a UserForm with more than 150 Combobox and arround 200 Labels.

I want to set the charasteristic and design of the comboboxes in only one and i want to be applied in a lot of them, so i do not want to repeat the code hundreds of times.

How should i do? i ve been reading but i cant match the examples with mine.

This is the wrong code:

Private Sub ComboBox7_Change()
    Dim ws2 As Worksheet: Set ws2 = Sheets("C. VfM Questionnaire ")
    Dim i, p As Integer

    For i = 7 To 31
            If Controls("ComboBox" & i).Value = "Yes" Then
        ws2.Range("G7") = 4
        Me.Controls("Label" & i).BackColor = RGB(146, 208, 80)

        ElseIf Controls("ComboBox" & i).Value = "No" Then
        ws2.Range("G7") = 4
        Me.Controls("Label" & i).BackColor = RGB(255, 33, 26)

        ElseIf Controls("ComboBox" & i).Value = "Not Applicable" Then
        ws2.Range("G7") = 4
        Me.Controls("Label" & i).BackColor = RGB(255, 204, 0)
        End If
    Next i

End Sub

Also i do not know how to trigger the code? is it in te user form initalize or in a module? Im so newbee as you can see!

Thanks a lot in advance.

What do you mean _This is the wrong code_? Do you get an error? Does it not do what you want it to do? — Nicholas Hunter, May 22 '21 at 17:21

score 2 · Answer 1 · answered May 22 '21 at 23:20

If I understood your question correctly, you have a lot of ComboBoxes, and you want to change their style. But instead of changing all of them, you want to change one of them, and then have the form apply this style to all of them, right?

So we pick a box to be the "style guide" and assign that to a variable.
Then we loop though all the ComboBoxes in the Form, and apply the traits.
As for how to trigger it, UserForm initalize would certainly work.

This code for example:

Private Sub UserForm_Initialize()
Dim origin As ComboBox, c As Variant

Set origin = Me.ComboBox1 'Combobox to copy style from

For Each c In Me.Controls
    If TypeName(c) = "ComboBox" Then
        c.BackColor = origin.BackColor
        c.BackStyle = origin.BackStyle
        c.BorderColor = origin.BorderColor
        c.BorderStyle = origin.BorderStyle
        c.ForeColor = origin.ForeColor
    End If
Next
End Sub

Changes this:

To this:

Applying the style of the first box, in the top left corner, called ComboBox1

Amazing thank you very much i will check if it works! I believe it will! — atallpa, May 25 '21 at 11:10

How should I Use 1 code for same Objects in VBA Excel

1 Answers1