Could this be written in a shorter way

[DB73]

Can anyone help me with this one?

I've got a userform combobox (dropdown) with about 6 items, but there are 3 value's in, that when sellected, the other userform comboboxes needs to be blocked (disabled) and the back color turns to grey

i've got the following code, that works, but there is another value/text in combobox1 that when sellected it has to do the same.
The values are ziek, verlof, feestdag.

do i have to write this code 3 times, eachother with another value/text or is there a shortcut

Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = "verlof" Then
Me.ComboBox2.Enabled = False
Me.ComboBox3.Enabled = False
Me.ComboBox4.Enabled = False
Me.ComboBox5.Enabled = False
Me.ComboBox6.Enabled = False
Me.ComboBox7.Enabled = False
Me.ComboBox8.Enabled = False
Me.ComboBox9.Enabled = False
Me.ComboBox10.Enabled = False
Me.ComboBox11.Enabled = False
Me.ComboBox12.Enabled = False
Me.ComboBox13.Enabled = False
Me.ComboBox14.Enabled = False
Me.ComboBox15.Enabled = False
Me.ComboBox16.Enabled = False
Me.ComboBox17.Enabled = False

Me.ComboBox2 = "-"
Me.ComboBox3 = "-"
Me.ComboBox4 = "-"
Me.ComboBox5 = "-"
Me.ComboBox6 = "-"
Me.ComboBox7 = "-"
Me.ComboBox8 = "-"
Me.ComboBox9 = "-"
Me.ComboBox10 = "-"
Me.ComboBox11 = "-"
Me.ComboBox12 = "-"
Me.ComboBox13 = "-"
Me.ComboBox14 = "-"
Me.ComboBox15 = "-"
Me.ComboBox16 = "-"
Me.ComboBox17 = "-"

Me.ComboBox2.BackColor = RGB(224, 224, 224)
Me.ComboBox3.BackColor = RGB(224, 224, 224)
Me.ComboBox4.BackColor = RGB(224, 224, 224)
Me.ComboBox5.BackColor = RGB(224, 224, 224)
Me.ComboBox6.BackColor = RGB(224, 224, 224)
Me.ComboBox7.BackColor = RGB(224, 224, 224)
Me.ComboBox8.BackColor = RGB(224, 224, 224)
Me.ComboBox9.BackColor = RGB(224, 224, 224)
Me.ComboBox10.BackColor = RGB(224, 224, 224)
Me.ComboBox11.BackColor = RGB(224, 224, 224)
Me.ComboBox12.BackColor = RGB(224, 224, 224)
Me.ComboBox13.BackColor = RGB(224, 224, 224)
Me.ComboBox14.BackColor = RGB(224, 224, 224)
Me.ComboBox15.BackColor = RGB(224, 224, 224)
Me.ComboBox16.BackColor = RGB(224, 224, 224)
Me.ComboBox17.BackColor = RGB(224, 224, 224)

End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub ComboBox1_Change()
   Dim i As Long
   If Me.ComboBox1.Value = "verlof" Then
      For i = 2 To 17
         With Me.Controls("ComboBox" & i)
            .Enabled = False
            .Value = "-"
            .BackColor = RGB(224, 224, 224)
         End With
      Next i
   End If
End Sub

 

[DB73]

Hi & welcome to MrExcel
How about

Private Sub ComboBox1_Change()
   Dim i As Long
   If Me.ComboBox1.Value = "verlof" Then
      For i = 2 To 17
         With Me.Controls("ComboBox" & i)
            .Enabled = False
            .Value = "-"
            .BackColor = RGB(224, 224, 224)
         End With
      Next i
   End If
End Sub

Thanks.....that was quick...
But than the for the other question..
Combox1 has 3 values that has to done the same way.
Do i have to write this code 3 times...for each value ? Or is it possible to write "ziek" , " verlof" , " feestdag" in this one.

Thank for the help anyway..

 

[Fluff]

Do you need to do the same thing for each of those values?

 

[DB73]

for the values;
ziek
verlof
feestdag

but.....if i choose 1 op those options, i want tho have the option to chose another 1 in combobox1...sometime wrong clicking by me
but now i can change the options in the pull down (combobox), but the rest of the comboboxes stay disabled en backcolor grey

maybe a code to grey out and disable all comboboxes untill the correct value is choosen in combobox1....just a way arround...
i have 2 values for that.

also. if i close the form with commandbutton (code; hide.me) and open it again there still disabled and grey.
i have to stop the marco on the vba working tab

 

[Fluff]

How about

Private Sub ComboBox1_Change()
   Dim i As Long
   If Me.ComboBox1.Value = "verlof" Then
      For i = 2 To 17
         With Me.Controls("ComboBox" & i)
            .Enabled = False
            .Value = "-"
            .BackColor = RGB(224, 224, 224)
         End With
      Next i
   Else
      For i = 2 To 17
         With Me.Controls("ComboBox" & i)
            .Enabled = True
            .Value = ""
            .BackColor = RGB(255, 255, 255)
         End With
      Next i
   End If
End Sub

 

[Alex Blakenburg]

Slight modification to @Fluff's code.

Private Sub ComboBox1_Change()
    Dim i As Long

    Select Case Me.ComboBox1.Value
        Case "ziek", "verlof", "feestdag"
            For i = 2 To 17
                With Me.Controls("ComboBox" & i)
                   .Enabled = False
                   .Value = "-"
                   .BackColor = RGB(224, 224, 224)
                End With
            Next i
        
        Case Else
          For i = 2 To 17
             With Me.Controls("ComboBox" & i)
                .Enabled = True
                .Value = ""
                .BackColor = RGB(255, 255, 255)
             End With
          Next i
    End Select

End Sub

You could also have used the below if you prefer that.

    If Me.ComboBox1.Value = "ziek" Or _
        Me.ComboBox1.Value = "verlof" Or _
        Me.ComboBox1.Value = "feestdag" Then

 

[DB73]

How about

Private Sub ComboBox1_Change()
   Dim i As Long
   If Me.ComboBox1.Value = "verlof" Then
      For i = 2 To 17
         With Me.Controls("ComboBox" & i)
            .Enabled = False
            .Value = "-"
            .BackColor = RGB(224, 224, 224)
         End With
      Next i
   Else
      For i = 2 To 17
         With Me.Controls("ComboBox" & i)
            .Enabled = True
            .Value = ""
            .BackColor = RGB(255, 255, 255)
         End With
      Next i
   End If
End Sub

thanks

 

[Fluff]

Glad we could help & thanks for the feedback.