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

[LNG2013]

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
 
DataReport.ComboBox1.Clear
    Columns("N:N").Select
    Range("A2:HX29921").Sort Key1:=Range("N2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Data5m.Range("A1").AutoFilter
Call FillCombobox(Data5m.Range("N2", Data5m.Cells(Rows.Count, "N").End(xlUp)), DataReport.ComboBox1)
DataReport.Show
End Sub

Forms - DataReport

Private Sub ComboBox1_Change()  'was A, now N
    DataReport.ComboBox2.Clear
    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
    DataReport.ComboBox3.Clear
    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
    DataReport.ComboBox4.Clear
    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

 

[Trevor G]

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

 

[Noz2k]

Or you can use the .enabled property in the same way I think. That should automatically grey out the comboboxes.

 

[LNG2013]

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.

 

[Trevor G]

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

 

[LNG2013]

The enable property worked very well thank you!

 

[Trevor G]

Pleased to read you have solution.

Thanks for the feedback