Userform dependent Comboboxes

[Apple08]

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.

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]

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.

 

[Fluff]

Yes you can use If statements to get the relevant data

 

[Apple08]

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.

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]

You cannot put them all on one line like that, you need to have each on it's own line & without the Ands

 

[Apple08]

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

[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]

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]

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]

Glad you got it sorted & thanks for the feedback