VBA If statement to apply to every combobox within Userform

[lancs83]

Hi,

Really hope someone can help with this please.

I am creating a Userform with dozens of ComboBoxes to aid data input of job booking. There are 4 types of job, and each job turns the relevant ComboBox a particular colour, like so:

If ComboBox6.Value = "LFT" Then
ComboBox6.BackColor = RGB(255, 201, 14)
Else
End If
If ComboBox6.Value = "EXT" Then
ComboBox6.BackColor = RGB(163, 73, 164)
Else
End If

It would be a great timesaver if I didn't have to copy and paste, then change the code, for every ComboBox. Is there a way I could do an If statement that would apply to every ComboBox within my Userform. Sort of:

If any ComboBox.value within Userform7 = "" Then...

 

[diddi]

hi and welcome to MrExcel. se if this does what you want

Sub CheckCombos()
    Dim ctrl as msforms.control

    For Each ctrl In Me.Controls
        If TypeOf ctrl Is msforms.combobox Then
            Select Case ctrl.Value
                Case "Ext"
                    ctrl.BackColor = RGB(163, 73, 164)
                Case "LFT"
                    ctrl.BackColor = RGB(255, 201, 14)
                Case "the other 1 "
                    ctrl.BackColor = RGB()
                Case "the other other 1"
                    ctrl.BackColor = RGB()
            End Select
        End If
    Next
End Sub

this needs to be triggered by an event. you can use combobox1_change etc, or you could turn the code into a class which is more elegant, but more complex to do

 

[lancs83]

Thanks diddi - you are an absolute genius!! You have just saved me a full day of copy, pasting, and altering code. Thank you so much.

I have pasted this into my Userform_Initialize code and it has worked perfectly.

Sorry to come back with an immediate follow up question, but it would also be great if I could tailor this code to run whenever any of the ComboBox values are changed. Is this possible please?

 

[lancs83]

Sorry - I just realised that if paste your code into a UserForm_Click() function then all my dreams come true.

Thanks again diddi - really appreciate you taking the time to answer my post.

 

[diddi]

it is possible to make it work on the comboboxes change only but it takes a bit of extra coding which is a bit more complex. but i am glad you have a suitable solution. cheers