January 28, 2016

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

Download a sample file for practice:

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

  1. 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



    i = i + 1

    End If


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


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

    End If

    End Sub

  2. 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.

    Akbar Alam

Comments are closed.