Create dependent combo boxes in Excel user form with VBA

We can create dependent combo boxes in Excel user form with VBA quickly and then use the displayed to perform more actions like for example checking available quantities of items in our inventory. Watch the training video below before delving into the VBA macro code solution:

Watch the video on YouTube.

The complete VBA code:

Private Sub ComboBox1_Change()
Me.ComboBox2 = “”
Select Case Me.ComboBox1
Case “Department”
Me.ComboBox2.RowSource = “Department”

Case “Admin”
Me.ComboBox2.RowSource = “Admin”

Case “R_D”
Me.ComboBox2.RowSource = “R_D”

Case “Marketing”
Me.ComboBox2.RowSource = “Marketing”

Case “Sales”
Me.ComboBox2.RowSource = “Sales”
End Select

End Sub

Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim lastrow As Long
Dim lastcolumn As Long
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets(“sheet1”)
    For I = 1 To lastcolumn
With .Columns(I)
lastrow = Sheet1.Cells(Rows.Count, I).End(xlUp).Row
With Range(Cells(1, I), Cells(lastrow, I))
Range(Cells(1, I), Cells(lastrow, I)).Select
Selection.CreateNames Top:=True
End With
End With
Next I
End With
Me.ComboBox1.RowSource = “Department”
End Sub

5 thoughts on “Create dependent combo boxes in Excel user form with VBA

  1. Guruprasad

    Hi Sir,

    I need your help in one of the situation which I stuck with.

    1) Have created a form called “Main Menu” and I have a command button as Admin Login.
    2) Once I click on Admin Login command button it enables me with Frame3 in the same form which I was given visible false initially.
    3) Frame3 control has two label , two text box and two command button, these controls is to login with admin user id and password.
    4) Below the code I used in this form for admin login authentication. I want program to show Frame2 and hide this login frame3. However Once I given correct user id and password program is getting stoped automatically and not sure why this happending and where is the wrong happened.

    Please help on this and below is the code which I used here.

    Private Sub Admin_Login_Click()
    Dim username As String
    Dim password As String
    Dim i As Integer

    username = TextBox6.Text
    password = TextBox5.Text

    i = 9

    Do While Cells(i, 10).Value “”

    If Cells(i, 10).Value = username And Cells(i, 11).Value = password Then
    Frame2.Visible = True
    Frame3.Visible = False

    End

    Else

    i = i + 1

    End If

    Loop

    If TextBox6.Text = “” Then
    MsgBox “Enter User ID”
    TextBox6.SetFocus
    ElseIf TextBox5.Text = “” Then
    MsgBox “Enter Password”
    TextBox5.SetFocus

    Else

    MsgBox “Please check your username and password”
    TextBox6.Text = “”
    TextBox5.Text = “”
    TextBox6.SetFocus

    End If

    End Sub

    Reply
  2. Akbar Alam

    Hi sir ,
    I am trying the same format as you have used in dependent combo boxes in excel user form with vba. I am getting an error message as Run time error 438
    Object doesn’t support this property or method and I am unable to create to the list from Name Manager as well with the code.

    Plz help.

    Rgds
    Akbar Alam

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *