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
– Nicholas Hunter, Commented May 22, 2021 at 17:21

Christofer Weber · Accepted Answer · 2021-05-22 23:20:34Z

2

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

answered May 22, 2021 at 23:20

Christofer Weber

1,4741 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

atallpa Over a year ago

Amazing thank you very much i will check if it works! I believe it will!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related