Enable And Disable Controls Based On Selection In Previous Combo Box

**paul_pearson** · 08-21-2013, 05:53 AM

Hi

With the userform in the "Category" Combobox if "Internal" is selected then the "External" Combobox entries are not visible/cannot be selected...if Category "External" selected then "Internal" Combobox entries are not visible/cannot be selected....
Also with the above can the selections in all box`s in userform only be made in order.So a date must be entered first,machine second,Category third....etc..etc..if a box is selected before a entry is entered in any box above it then that box entries remain invisible until entries are entered in order starting at the very top....a message boxs opens saying entry must be made in Date,Machine,Category etc..etc..

Thanks

Paul

**Excel Fox** · 08-21-2013, 11:06 PM

Here's what I've added to your code

Code:

Private Sub ComboBox1_Change()

    ComboBox2.Enabled = ComboBox1.Text <> ""
    
End Sub

Private Sub ComboBox2_Change()

    ComboBox3.Enabled = ComboBox2.Text <> ""

End Sub

Private Sub ComboBox3_Change()

    Dim lngCol As Long
    With Worksheets("sample")
        lngCol = .Rows(2).Find(What:=Me.ComboBox3.Text, lookAt:=xlWhole).Column
        Me.ComboBox4.List = .Range("A4:A" & .Cells(.Rows.Count, lngCol).End(xlUp).Row).Offset(, lngCol - 1).Value2
        Me.ComboBox5.List = .Range("A4:A" & .Cells(.Rows.Count, lngCol + 1).End(xlUp).Row).Offset(, lngCol).Value2
        Me.ComboBox6.List = .Range("A4:A" & .Cells(.Rows.Count, lngCol + 2).End(xlUp).Row).Offset(, lngCol + 1).Value2
        Me.ComboBox7.List = .Range("A4:A" & .Cells(.Rows.Count, lngCol).End(xlUp).Row).Offset(, lngCol - 1).Value2
        Me.ComboBox8.List = .Range("A4:A" & .Cells(.Rows.Count, lngCol + 1).End(xlUp).Row).Offset(, lngCol).Value2
    End With
    Frame1.Enabled = UCase(ComboBox3.Text) = "INTERNAL"
    Frame2.Enabled = Not Frame1.Enabled
    ComboBox4.Text = ""
    ComboBox5.Text = ""
    ComboBox6.Text = ""
    ComboBox7.Text = ""
    ComboBox8.Text = ""
    
End Sub

In addition, I've also changed the style of your drop-down box to List-box instead of Combo-box. That way, user cannot 'type' anything that is not there in the list. In addition, I've also changed the Enabled property to false, and based on selection of value in the hierarchical order, the combo-boxes will get enabled.