So I have this VBA code for setting up dependent combo boxes. However, the combobox2 is always blank. Not sure what I am going wrong. I have dependent drop downs that work great in the sheet using these formulas...=DDD_Expense_Categories for the first drop down and....=XLOOKUP($F$2, DDD_Expense_Categories, DDD_for_List, "", 0) for the second dependent drop down where F2 is the value in the first drop down. Below is the vba code. Any help or suggestions is greatly appreciated.
VBA Code:
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim category As Range
' Set the worksheet where your named ranges are located
Set ws = ThisWorkbook.Sheets("Expense Categories") ' Ensure this matches your actual sheet name
' Clear Form
ComboBox1.Clear
ComboBox2.Clear
' Populate ComboBox1 with items from the DDD_Expense_Categories named range
On Error Resume Next ' Handle error if named range is not found
For Each category In ws.Range("DDD_Expense_Categories")
If category.value <> "" Then
ComboBox1.AddItem category.value
End If
Next category
On Error GoTo 0 ' Resume normal error handling
End Sub
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim lookupValue As String
Dim lookupRange As Range
Dim namedRange As String
Dim xlookupFormula As String
' Set the worksheet containing the lookup table for categories and ranges
Set ws = ThisWorkbook.Sheets("Expense Categories") ' Ensure this is the correct sheet
' Get the selected value from ComboBox1
lookupValue = ComboBox1.value
Debug.Print "Lookup Value: " & lookupValue ' Debug statement
' Clear ComboBox2 before populating it
ComboBox2.Clear
' Define XLOOKUP formula to get the named range based on ComboBox1 selection
' Assuming you have a lookup table in columns A and B where A has categories and B has corresponding named ranges
' For example, A2:A20 contains "Income: Client_1_Name", "Emergency", etc.
' B2:B20 contains corresponding named ranges like "Client1_Income_Tbl", "Emergency_Tbl", etc.
xlookupFormula = "=XLOOKUP(lookupValue, DDD_Expense_Categories, DDD_for_List, "", 0)"
' Use VBA to evaluate the XLOOKUP formula and retrieve the named range
On Error Resume Next
namedRange = ws.Evaluate(xlookupFormula)
On Error GoTo 0
' If a named range is found, populate ComboBox2 with values from that range
If Len(namedRange) > 0 Then
' Set the range to populate ComboBox2
Set lookupRange = ws.Range(namedRange)
' Add each value from the range to ComboBox2
For Each cell In lookupRange
If cell.value <> "" Then
ComboBox2.AddItem cell.value
End If
Next cell
Else
Debug.Print "No valid named range found for the selected value."
End If
End Sub
Last edited by a moderator:
