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 = “”
Me.ComboBox2 = “”
Select Case Me.ComboBox1
Case “Department”
Me.ComboBox2.RowSource = “Department”
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”)
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
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
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
Thanku Soo Much I have Solved the Problem
When I set the rowsource using the code it is still showing me blank combo box. Any reasons for that sir?
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
Hello
I need help regarding to dependent combobox , is there any one help me?