Userform gray out buttons and selectors until selection made - Code Provided


Active Member
May 23, 2011
I have a userform with 4 CombBoxes. I want to be able to have the ComboBoxes for each grayed out until a selection is made. The first one is the only one that should be ungrayed. Once the 3rd combobox is selected I want to give the ability to Run the report, and thus ungray the "Run" button. Is this possible in excel?

Microsoft Excel Objects - ThisWorkbook code

'The following is for the Userform DataReport code
    Range("A2:HX29921").Sort Key1:=Range("N2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Call FillCombobox(Data5m.Range("N2", Data5m.Cells(Rows.Count, "N").End(xlUp)), DataReport.ComboBox1)
End Sub

Forms - DataReport
Private Sub ComboBox1_Change()  'was A, now N
    If Data5m.FilterMode = True Then: Data5m.ShowAllData
    Data5m.Range("A1").AutoFilter field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
    Call FillCombobox(Data5m.Range("X2", Data5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox2)
End Sub
Private Sub ComboBox2_Change()  'was EJ, now X
    Data5m.Range("A1").AutoFilter field:=24, Criteria1:="=" & DataReport.ComboBox2.Value
    Call FillCombobox(Data5m.Range("P2", Data5m.Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox3)
End Sub
Private Sub ComboBox3_Change()  'was ED, now P
    Data5m.Range("A1").AutoFilter field:=16, Criteria1:="=" & DataReport.ComboBox3.Value
    Call FillCombobox(Data5m.Range("Q2", Data5m.Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox4)
End Sub
Private Sub ComboBox4_Change()  'was EI, now Q
    Data5m.Range("A1").AutoFilter field:=17, Criteria1:="=" & DataReport.ComboBox4.Value
End Sub
Private Sub RunButton_Click()
Unload DataReport
Call Filtered
Call AMasterBuild
End Sub
Private Sub CancelButton_Click()
Unload DataReport
End Sub

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You can use the lock property and add some colour, then in the combo box you can use the change event.

Sample below.

Private Sub ComboBox1_Change()
Me.ComboBox2.Locked = False
End Sub

Private Sub UserForm_Initialize()
Me.ComboBox2.Locked = True
Me.ComboBox3.Locked = True
Me.ComboBox4.Locked = True
Me.CommandButton1.Locked = True
End Sub
Upvote 0
Or you can use the .enabled property in the same way I think. That should automatically grey out the comboboxes.
Upvote 0
Thanks that worked well. Could you give an example of how to change the color of the combobox, and then how to set it back to default.
Upvote 0
As suggested the enable will automatically gray out the objects

Private Sub UserForm_Initialize()
Me.ComboBox2.Enabled = False
Me.ComboBox3.Enabled = False
Me.CommandButton1.Enabled = False
End Sub
Upvote 0
Pleased to read you have solution.

Thanks for the feedback;)
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back