Excel UserForm Dependent ComboBoxes

B

benntw

Board Regular
Joined
Feb 17, 2014
Messages
222
Office Version
  1. 365
Platform
  1. Windows
I have researched Excel userforms for hours looking for a way to make a combobox dependent on another combobox.

For ComboBox 6 my named range is ProjectNumber coming from my Lists Tab column E. Currently there are 8383 values on the sheet.
For ComboBox 7 my named range is TaskNumber coming from the same list tab column F. Same amount of entries.

ComboBox 6 Row Source is ProjectNumber
ComboBox 7 Row Source is TaskNumber

Both of these lists will continue to grow since they are linked to another program for updates. There are multiple duplicate Project numbers. But, the multiple numbers have unique task numbers.

What I need to do is once ComboBox 6 is populated on the userform I want the list of Task numbers to only show that are next to the Project numbers to populate ComboBox 7.

Is there a way to do this ? Everything I found didn't work with using row source and the ones I found put names in quotes. All involved Case select with names. If there isn't a way to do this the only option I have is to have one combobox with the project numbers & task numbers combined which will make it more cumbersome to select.

I appreciate the help with this.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Try something like this:

Code: 
Private Sub UserForm_Initialize()
    With Worksheets("List")
        ComboBox6.List = .Range("E1:F" & .Range("E" & .Rows.Count).End(xlUp).Row).Value
        ComboBox7.List = .Range("F1:F" & .Range("F" & .Rows.Count).End(xlUp).Row).Value
    End With
End Sub
 
Upvote 0
I get run-time error '9': Subscript out of range. When I put the code it it won't let me show the userform.
 
Upvote 0
I made a mistake I belive:
This script assumed you have a sheet named "List"
And a Combobox named Combobox6 and combobox7
And you must have values in column "E" and "F"

Code: 
Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Sheets("List").Cells(Rows.Count, "E").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets("List").Cells(Rows.Count, "F").End(xlUp).Row
    
        ComboBox6.List = Sheets("List").Range("E1:E" & Lastrow).Value
        ComboBox7.List = Sheets("List").Range("F1:F" & Lastrowa).Value
End Sub
 
Last edited:
Upvote 0
I appreciate the help. Now I am getting error 70 permission denied. It won't show userform.
 
Upvote 0
I've done my best. I have no other answer. I test all my scripts. I assume if your making userforms you know all about scripting and having macro enabled workbooks. Most people never start out making userforms so I'm not sure what to say. Maybe someone else here at Mr. Excel will have an answer. And I assume your using a PC and not a Mac.
benntw said:
I appreciate the help. Now I am getting error 70 permission denied. It won't show userform.
Click to expand...
 
Last edited:
Upvote 0
Thank you very much. I really do appreciate you trying to help with this. I have a macro that opens the userform and that is what has been giving me trouble with the code. I have the ComboBoxes being populated from row source. I've never tried dependent boxes before. A lot easier using data validation with indexing on a sheet than trying to get comboboxes to be dependent. Again thank you for the time you spent.
 
Upvote 0
benntw,

Remove the RowSource for both combo boxes.
Try this in the form Initialize, to populate combo6 only, adjust sheet name as required.
Code: 
Private Sub UserForm_Initialize()
    Dim lr As Long, i As Long
    Dim dic As Object
    Dim arr As Variant
    
With Sheets("Sheet1")
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    'load array for speed
    arr = .Range("E2:E" & lr)
End With

Set dic = CreateObject("Scripting.Dictionary")
'use dictionary for uniques
For i = 1 To UBound(arr, 1)
  dic(arr(i, 1)) = 1
Next i
'populate combo
Me.ComboBox6.List = Application.Transpose(dic.keys)

End Sub

and use the Change event of combobox6 to populate combobox7
Code: 
Private Sub ComboBox6_Change()
    Dim lr As Long, i As Integer
    Dim filtRng As Range, cel As Range
    
Application.ScreenUpdating = False

'remove existing list from combobox7
With Me.ComboBox7
    .Value = ""
    For i = .ListCount - 1 To 0 Step -1
        .RemoveItem i
    Next i
End With

'establish range to filter
With Sheets("Sheet1")
     lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
     Set filtRng = .Range("E1:E" & lr)
End With

'filter the range
filtRng.AutoFilter field:=1, Criteria1:=Me.ComboBox6.Value, VisibleDropDown:=False
'add items to combo list
For Each cel In filtRng.Offset(1, 1).SpecialCells(xlCellTypeVisible).Cells
    If cel.Value <> "" Then
        Me.ComboBox7.AddItem cel.Value
    End If
Next cel
'remove filter
filtRng.AutoFilter

Application.ScreenUpdating = True

End Sub

Hope that helps.
 
Last edited:
Upvote 0
I need to make sure that I am not doing something wrong. To open my form I have it under a macro. Code is below. When I try and open it after the code you gave me it says error 9.

Sub DisplayUserform()
UserForm1.Show
End Sub
 
Upvote 0
Show us the code you have in your Initilize script.
benntw said:
I need to make sure that I am not doing something wrong. To open my form I have it under a macro. Code is below. When I try and open it after the code you gave me it says error 9.

Sub DisplayUserform()
UserForm1.Show
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Dynamic Named Range not working with Userform ComboBox
Replies
6
Views
454
NoSparks
N
P
  • Question
Check one Combobox value against another Combobox value
Replies
6
Views
238
Pacman52
P
W
  • Question
Saving userform data into specific row
Replies
1
Views
187
dmt32
D
W
  • Solved
pop up msgbox dependent on specific text entered into userform combobox
Replies
3
Views
275
Wishful Thinking
W
W
  • Locked
  • Question
additional pop up msgbox dependent on specific text entered into userform combobox, and other questions
Replies
2
Views
265
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,036
Messages
6,122,794
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top