Userform dependent Comboboxes

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
Hi All

I would like to setup an userform having multiple dependent comboboxes.
Data is retrieved from the Sheets(“Summary”):
column A is duplicate value of portfolio names
column C is duplicate value of project names
column E to AA to show the budget data for the selectedproject.


I have tried the following code to filter portfolio andproject names, however only the portfolio name filter works, but not theproject name.


I just copied code into my project, so I hope someone cantell me what I have done wrong, and what is the best way to do after a projectname is selected from the combobox, then the budget data is shown foredit? Many thanks.




Code:
Private Sub UserForm_Initialize()
    Dim lr As Long, i As Long
    Dim dic As Object
    Dim arr As Variant
    
With Sheets("Summary")
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    'load array for speed
    arr = .Range("A3:A" & 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.ComboBox1.List = Application.Transpose(dic.keys)
End Sub
Private Sub ComboBox1_Change()
    Dim lr As Long, i As Integer
    Dim filtRng As Range, cel As Range
    
Application.ScreenUpdating = False
'remove existing list from combobox2
With Me.ComboBox2
    .Value = ""
    For i = .ListCount - 1 To 0 Step -1
        .RemoveItem i
    Next i
End With
'establish range to filter
With Sheets("Summary")
     lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
     Set filtRng = .Range("C3:C" & lr)
End With
'filter the range
filtRng.AutoFilter field:=1, Criteria1:=Me.ComboBox1.Value, VisibleDropDown:=False
'add items to combo list
For Each cel In filtRng.Offset(1, 2).SpecialCells(xlCellTypeVisible).Cells
    If cel.Value <> "" Then
        Me.ComboBox2.AddItem cel.Value
    End If
Next cel
'remove filter
filtRng.AutoFilter
Application.ScreenUpdating = True
End Sub
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
I need to match with different sheets to retrieve data, once a Portfolio, project and financial year are selected then I need to retrieve budget data from sheet budget, other monthly data from sheet summary. Therefore I wonder can I use if function in macro to retrieve data in matching.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
Yes you can use If statements to get the relevant data
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
Thanks Fluff, I have done the code below but it didn't work, it works with only the TextBox1.value is added, but it didn't work after I added in Textbox2 to 5. Please could you let me know what I have done wrong? Thanks.


Code:
Private Sub ComboBox2_Change() 
'input budget
Sheets("Budget").Select
Dim i As Long
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
 
For i = 2 To lastrow
 
If ComboBox2.Value = Cells(i, 4) And ComboBox3.Value = Cells(i, 1) Then
TextBox1.Value = Cells(i, 5) And TextBox2.Value = Cells(i, 6) And TextBox3.Value = Cells(i, 7) And TextBox4.Value = Cells(i, 8) And TextBox5.Value = Cells(i, 9)
 
End If
 
Next i
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
You cannot put them all on one line like that, you need to have each on it's own line & without the Ands
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404

ADVERTISEMENT

Thanks Fluff, I have amended code below, however only TextBox 3-5 work, but not TextBox1 and 2, have I done something wrong:

Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub ComboBox2_Change()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'input budget[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Budget").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim i As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim lastrow As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]lastrow = Range("A" &Rows.Count).End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For i = 2 To lastrow[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If ComboBox2.Value = Cells(i, 4) And ComboBox3.Value =Cells(i, 1) Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]TextBox1.Value = Cells(i, 5)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]TextBox2.Value = Cells(i, 6)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]TextBox3.Value = Cells(i, 7)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]TextBox4.Value = Cells(i, 8)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]TextBox5.Value = Cells(i, 9)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
There's nothing obviously wrong with that code.
Do you get any error messages? or is it that Textboxes 1 & 2 simply don't get filled?
If the latter are you sure that columns E & F have a value for that row?
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
I just noticed the texbox numbers have been changed while I moved around textboxes. They work now. Sorry for the confusion. Many thanks for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted & thanks for the feedback
 

Forum statistics

Threads
1,147,962
Messages
5,744,059
Members
423,843
Latest member
alex2022

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
Top