VBA in User Form. Xlookup formula for Dependent Combo Box.

Kscoof

New Member
Joined
Dec 23, 2022
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I made some adjustments to your code.
I replaced XLOOKUP with the Find method, I find it more efficient.
I also removed the On Error statements as it is not recommended to use them as different errors can occur and you will not know what the real problem is. As far as possible, use code to verify possible data errors and thus have a more efficient code.

VBA Code:
Private Sub UserForm_Initialize()
  Dim ws As Worksheet
  Dim category As Range
  Dim namedCat As String
  
  ' Set the worksheet where your named ranges are located
  Set ws = ThisWorkbook.Sheets("Expense Categories") ' Ensure this matches your actual sheet name
  
  ' Clear Form (It is not necessary, at the beginning the controls are empty)
  'ComboBox1.Clear
  'ComboBox2.Clear
  
  ' Populate ComboBox1 with items from the DDD_Expense_Categories named range
  namedCat = "DDD_Expense_Categories"
  If Evaluate("IsRef(" & namedCat & ")") Then
    For Each category In ws.Range(namedCat)
      If category.Value <> "" Then
        ComboBox1.AddItem category.Value
      End If
    Next category
  End If
End Sub

Private Sub ComboBox1_Change()
  Dim ws As Worksheet
  Dim cell As Range, f As Range
  Dim namedCat As String, namedRange As String
  Dim ini As Long
  
  ' Set the worksheet containing the lookup table for categories and ranges
  Set ws = ThisWorkbook.Sheets("Expense Categories") ' Ensure this is the correct sheet
  
  ' Clear ComboBox2 before populating it
  ComboBox2.Clear
  
  ' Search ComboBox1 in Categories range
  If ComboBox1.ListIndex = -1 Then Exit Sub
  namedCat = "DDD_Expense_Categories"
  Set f = Range(namedCat).Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
  If f Is Nothing Then
    MsgBox "Category does not exist"
    Exit Sub
  End If
  
  ' Get the named range based on ComboBox1 selection
  ini = Range(namedCat).Cells(1).Row - 1
  namedRange = Range("DDD_for_List").Cells(f.Row - ini)
  
  ' If a named range is found, populate ComboBox2 with values from that range
  If Evaluate("IsRef(" & namedRange & ")") Then
  
    ' Add each value from the range to ComboBox2
    For Each cell In ws.Range(namedRange)
      If cell.Value <> "" Then
        ComboBox2.AddItem cell.Value
      End If
    Next cell
    
  Else
    MsgBox "No valid named range found for the selected value."
  End If
End Sub
 
Upvote 0
1728063253136.png

First off, thanks for looking at this. When I used your version of the macro I get the error you inserted when I try to select combobox1. It has the values present in the dropdown but when I select one it gives the error.
 
Upvote 0
I stripped down the excel file to just the components of this form but I see there is no where to upload it to this forum. Or am I missing something. Just uploading a range won't work I think...
 
Upvote 0
Example.xlsm
ABC
1Enter Unique Recuring Items Here
2DescriptionNew CategorySub-Category
37-ElevenAutomotiveFuel/Tolls (All)
4Advance Auto PartsAutomotiveMaintenance (All)
5American FuelAutomotiveFuel/Tolls (All)
6American PetroleumAutomotiveFuel/Tolls (All)
7AmocoAutomotiveFuel/Tolls (All)
8Apex OilAutomotiveFuel/Tolls (All)
9ArcoAutomotiveFuel/Tolls (All)
10AutoZoneAutomotiveMaintenance (All)
11Beacon GasAutomotiveFuel/Tolls (All)
12Big Wave Car WashAutomotiveMaintenance (All)
13BlueSky GasAutomotiveFuel/Tolls (All)
14BlueSky PetroleumAutomotiveFuel/Tolls (All)
15Bob's Car WashAutomotiveMaintenance (All)
16BPAutomotiveFuel/Tolls (All)
17BP UltimateAutomotiveFuel/Tolls (All)
18Canadian TireAutomotiveMaintenance (All)
19Car Wash 821AutomotiveMaintenance (All)
20Car Wash USAAutomotiveMaintenance (All)
21Carquest Auto PartsAutomotiveMaintenance (All)
22ChevronAutomotiveFuel/Tolls (All)
23Circle KAutomotiveFuel/Tolls (All)
24CitgoAutomotiveFuel/Tolls (All)
25City Car WashAutomotiveMaintenance (All)
26City GasAutomotiveFuel/Tolls (All)
27ConocoAutomotiveFuel/Tolls (All)
28Conoco 76AutomotiveFuel/Tolls (All)
29ConocoPhillipsAutomotiveFuel/Tolls (All)
30Drive and ShineAutomotiveMaintenance (All)
31Family ExpressAutomotiveFuel/Tolls (All)
32Genuine Parts CompanyAutomotiveMaintenance (All)
33Jiffy LubeAutomotiveFuel/Tolls (All)
34Jubilee OilAutomotiveFuel/Tolls (All)
35Loves Travel StopsAutomotiveFuel/Tolls (All)
36Lynx GasAutomotiveFuel/Tolls (All)
37Mac's Convenience StoreAutomotiveFuel/Tolls (All)
38Majestic PetroleumAutomotiveFuel/Tolls (All)
39MapcoAutomotiveFuel/Tolls (All)
40MarathonAutomotiveFuel/Tolls (All)
41Marathon PetroleumAutomotiveFuel/Tolls (All)
42NAPA Auto PartsAutomotiveMaintenance (All)
43O'Reilly Auto PartsAutomotiveMaintenance (All)
44Pep BoysAutomotiveMaintenance (All)
45PhillipsAutomotiveFuel/Tolls (All)
46Phillips 66AutomotiveFuel/Tolls (All)
47RockAutoAutomotiveMaintenance (All)
48ShellAutomotiveFuel/Tolls (All)
49SpeedwayAutomotiveFuel/Tolls (All)
50SunocoAutomotiveFuel/Tolls (All)
51TexacoAutomotiveFuel/Tolls (All)
52Texaco HavolineAutomotiveFuel/Tolls (All)
53Tire RackAutomotiveMaintenance (All)
54ValeroAutomotiveFuel/Tolls (All)
55Acapulco Mexican RestaurantEntertainmentRestaurants
56Amazon PrimeEntertainmentNetflix, Amazon Prime, Audible, Etc.
57Applebee'sEntertainmentRestaurants
58AudibleEntertainmentNetflix, Amazon Prime, Audible, Etc.
59Bahama BreezeEntertainmentRestaurants
60Bar LouieEntertainmentRestaurants
61Big BoyEntertainmentRestaurants
62Bob EvansEntertainmentRestaurants
63Bonefish GrillEntertainmentRestaurants
64Buca di BeppoEntertainmentRestaurants
65Buffalo Wild WingsEntertainmentRestaurants
66Carrabba's Italian GrillEntertainmentRestaurants
67Chart HouseEntertainmentRestaurants
68Cheddar's Scratch KitchenEntertainmentRestaurants
69Chili's Grill & BarEntertainmentRestaurants
70ChristosEntertainmentRestaurants
71CinemaEntertainmentConcerts, Theater, Etc.
72******* BarrelEntertainmentRestaurants
73Del Frisco's GrilleEntertainmentRestaurants
74Denny'sEntertainmentRestaurants
75El ToritoEntertainmentRestaurants
76Famous Dave'sEntertainmentRestaurants
77Firebirds Wood Fired GrillEntertainmentRestaurants
78Fleming's Prime SteakhouseEntertainmentRestaurants
79Fogo de ChãoEntertainmentRestaurants
80Fox & HoundEntertainmentRestaurants
81Grimaldi's PizzeriaEntertainmentRestaurants
82HaciendaEntertainmentRestaurants
83Houlihan'sEntertainmentRestaurants
84HuluEntertainmentNetflix, Amazon Prime, Audible, Etc.
85IHOPEntertainmentRestaurants
86J. Alexander'sEntertainmentRestaurants
87Joe’s Crab ShackEntertainmentRestaurants
88Johnny RocketsEntertainmentRestaurants
89Kona GrillEntertainmentRestaurants
90Legal Sea FoodsEntertainmentRestaurants
91Logan's RoadhouseEntertainmentRestaurants
92LongHorn SteakhouseEntertainmentRestaurants
93Maggiano's Little ItalyEntertainmentRestaurants
94NetFlixEntertainmentNetflix, Amazon Prime, Audible, Etc.
95O'Charley'sEntertainmentRestaurants
96Olive GardenEntertainmentRestaurants
97Outback SteakhouseEntertainmentRestaurants
98P.F. Chang'sEntertainmentRestaurants
99Perkins Restaurant & BakeryEntertainmentRestaurants
100Rainforest CafeEntertainmentRestaurants
101Red LobsterEntertainmentRestaurants
102Romano’s Macaroni GrillEntertainmentRestaurants
103Ruby TuesdayEntertainmentRestaurants
104Shoney'sEntertainmentRestaurants
105Smokey Bones Bar & Fire GrillEntertainmentRestaurants
106Sullivan's SteakhouseEntertainmentRestaurants
107Ted's Montana GrillEntertainmentRestaurants
108Texas RoadhouseEntertainmentRestaurants
109TGI FridaysEntertainmentRestaurants
110The Cheesecake FactoryEntertainmentRestaurants
111The Melting PotEntertainmentRestaurants
112The Old Spaghetti FactoryEntertainmentRestaurants
113Waffle HouseEntertainmentRestaurants
114Zaxby'sEntertainmentRestaurants
115A&W RestaurantsFoodFast Food
116AcmeFoodGroceries
117ACME MarketsFoodGroceries
118AlbertsonsFoodGroceries
119Albertsons MarketFoodGroceries
120AldiFoodGroceries
121Arby'sFoodFast Food
122Auntie Anne'sFoodFast Food
123Auntie Anne's PretzelsFoodFast Food
124BagelsFoodFast Food
125Ben & Jerry'sFoodFast Food
126Black Bear DinerFoodFast Food
127Blaze PizzaFoodFast Food
128BlimpieFoodFast Food
129Bojangles'FoodFast Food
130Burger KingFoodFast Food
131BurgervilleFoodFast Food
132Café 21FoodFast Food
133Carl's Jr.FoodFast Food
134Charlie’s Philly SteaksFoodFast Food
135CheckersFoodFast Food
136Chick-fil-AFoodFast Food
137ChipotleFoodFast Food
138Chop'tFoodFast Food
139Church’s ChickenFoodFast Food
140CinnabonFoodFast Food
141CoffeeFoodFast Food
142CostcoFoodGroceries
143Culver'sFoodFast Food
144Dairy QueenFoodFast Food
145Dave & BustersFoodFast Food
146Del TacoFoodFast Food
147DillonsFoodGroceries
148Dollar GeneralFoodGroceries
149Domino'sFoodFast Food
150Dragon ExpressFoodFast Food
151Dunkin'FoodFast Food
152Earth FareFoodGroceries
153Einstein Bros. BagelsFoodFast Food
154El Pollo LocoFoodFast Food
155Family DollarFoodGroceries
156Famous Dave’sFoodFast Food
157Fareway StoresFoodGroceries
158Farm FreshFoodGroceries
159Farm Fresh Food & PharmacyFoodGroceries
160Fazoli'sFoodFast Food
161Firehouse SubsFoodFast Food
162Five GuysFoodFast Food
163Fleming'sFoodGroceries
164Food CityFoodGroceries
165Food GiantFoodGroceries
166Food LionFoodGroceries
167FoodlandFoodGroceries
168Fresh MarketFoodGroceries
169Fresh Thyme MarketFoodGroceries
170FreshcoFoodGroceries
171FuddruckersFoodFast Food
172Fuzzy's Taco ShopFoodFast Food
173Golden CorralFoodFast Food
174Great Harvest Bread Co.FoodFast Food
175Gyu-KakuFoodFast Food
176Hardee'sFoodFast Food
177In-N-Out BurgerFoodFast Food
178Jack in the BoxFoodFast Food
179Jamba JuiceFoodFast Food
180Jersey Mike'sFoodFast Food
181Jimmy John'sFoodFast Food
182Joe's Crab ShackFoodFast Food
183KFCFoodFast Food
184Krispy KremeFoodFast Food
185KrogerFoodGroceries
186Little CaesarsFoodFast Food
187Marco's PizzaFoodFast Food
188Martin'sFoodGroceries
189Martin's SupermarketFoodGroceries
190McDonald'sFoodFast Food
191MeijerFoodGroceries
192Meijer MarketplaceFoodGroceries
193Moe's Southwest GrillFoodFast Food
194MorrisonsFoodGroceries
195Mrs. FieldsFoodFast Food
196Orange JuliusFoodFast Food
197Owen'sFoodGroceries
198Panda ExpressFoodFast Food
199Panera BreadFoodFast Food
200PavilionsFoodGroceries
201Piggly WigglyFoodGroceries
202Pizza HutFoodFast Food
203PopeyesFoodFast Food
204Popeyes Louisiana KitchenFoodFast Food
205PretzelmakerFoodFast Food
206Price RiteFoodGroceries
207PublixFoodGroceries
208QDOBAFoodFast Food
209QFCFoodGroceries
210RalphsFoodGroceries
211Ralph's Fresh FareFoodGroceries
212Ralphs GroceryFoodGroceries
213Red RobinFoodFast Food
214Rita's Italian IceFoodFast Food
215Rite AidFoodGroceries
216SafewayFoodGroceries
217Sainsbury’sFoodGroceries
218Sam's ClubFoodGroceries
219Save A LotFoodGroceries
220SbarroFoodFast Food
221Shop 'n SaveFoodGroceries
222Shop 'n Save SupermarketFoodGroceries
223ShopRiteFoodGroceries
224Smoothie KingFoodFast Food
225SonicFoodFast Food
226StarbucksFoodFast Food
227Steak 'n ShakeFoodFast Food
228SubwayFoodFast Food
229SweetgreenFoodFast Food
230Taco BellFoodFast Food
231Taco John’sFoodFast Food
232Taco TimeFoodFast Food
233The Habit Burger GrillFoodFast Food
234The Halal GuysFoodFast Food
235Thrive MarketFoodGroceries
236Tim HortonsFoodFast Food
237Topper's PizzaFoodFast Food
238Trader Joe'sFoodGroceries
239Tutti FruttiFoodFast Food
240WalmartFoodGroceries
241WawaFoodFast Food
242WegmansFoodGroceries
243Wendy'sFoodFast Food
244WhataburgerFoodFast Food
245White CastleFoodFast Food
246Whole FoodsFoodGroceries
247WingstopFoodFast Food
248WingStreetFoodFast Food
249WingZoneFoodFast Food
25024 Hour FitnessSelfcareGym Memberships (All)
25124/7 FitnessSelfcareGym Memberships (All)
252Anytime FitnessSelfcareGym Memberships (All)
253Bally Total FitnessSelfcareGym Memberships (All)
254Bally'sSelfcareGym Memberships (All)
255CrossFitSelfcareGym Memberships (All)
256CrossFit GymSelfcareGym Memberships (All)
257Crossover FitnessSelfcareGym Memberships (All)
258FitnessSelfcareGym Memberships (All)
259Gold’s GymSelfcareGym Memberships (All)
260GymSelfcareGym Memberships (All)
261GymboxSelfcareGym Memberships (All)
262LA FitnessSelfcareGym Memberships (All)
263Life Fitness ClubSelfcareGym Memberships (All)
264MyFitnessPalSelfcareGym Memberships (All)
265New You FitnessSelfcareGym Memberships (All)
266Olympus GymSelfcareGym Memberships (All)
267Optimum FitnessSelfcareGym Memberships (All)
268OrangeTheory FitnessSelfcareGym Memberships (All)
269Performance FitnessSelfcareGym Memberships (All)
270Physical FitnessSelfcareGym Memberships (All)
271Planet FitnessSelfcareGym Memberships (All)
272Planet GymSelfcareGym Memberships (All)
273Powerhouse GymSelfcareGym Memberships (All)
274Total FitnessSelfcareGym Memberships (All)
275Ultimate FitnessSelfcareGym Memberships (All)
276YMCASelfcareGym Memberships (All)
277Abercrombie & FitchShoppingClothing
278AdidasShoppingClothing
279American Eagle OutfittersShoppingClothing
280AnthropologieShoppingClothing
281BalenciagaShoppingClothing
282Banana RepublicShoppingClothing
283BaubleBarShoppingClothing
284BelkShoppingClothing
285Bergdorf GoodmanShoppingClothing
286Beyond YogaShoppingClothing
287Bloomingdale'sShoppingClothing
288BodenShoppingClothing
289Bottega VenetaShoppingClothing
290BrooksShoppingClothing
291BurberryShoppingClothing
292ChacoShoppingClothing
293ChanelShoppingClothing
294ClarksShoppingClothing
295CoachShoppingClothing
296Columbia SportswearShoppingClothing
297Crate & BarrelShoppingClothing
298David's BridalShoppingClothing
299DieselShoppingClothing
300Dillard'sShoppingClothing
301Dolce & GabbanaShoppingClothing
302Dr. MartensShoppingClothing
303Eddie BauerShoppingClothing
304ExpressShoppingClothing
305FableticsShoppingClothing
306FarfetchShoppingClothing
307Forever 21ShoppingClothing
308GapShoppingClothing
309GucciShoppingClothing
310GymsharkShoppingClothing
311H&MShoppingClothing
312Helmut LangShoppingClothing
313HollisterShoppingClothing
314Hugo BossShoppingClothing
315J.CrewShoppingClothing
316JCPenneyShoppingClothing
317JockeyShoppingClothing
318Kohl'sShoppingClothing
319LL BeanShoppingClothing
320Lord & TaylorShoppingClothing
321Louis VuittonShoppingClothing
322LululemonShoppingClothing
323Macy'sShoppingClothing
324Marc JacobsShoppingClothing
325Michael KorsShoppingClothing
326Neiman MarcusShoppingClothing
327New BalanceShoppingClothing
328NikeShoppingClothing
329NordstromShoppingClothing
330On RunningShoppingClothing
331PandoraShoppingClothing
332PatagoniaShoppingClothing
333PINKShoppingClothing
334Pottery BarnShoppingClothing
335PradaShoppingClothing
336Ralph LaurenShoppingClothing
337Rebecca TaylorShoppingClothing
338Red Wing ShoesShoppingClothing
339Saint LaurentShoppingClothing
340Saks Fifth AvenueShoppingClothing
341Saks Off 5thShoppingClothing
342SperryShoppingClothing
343St. JohnShoppingClothing
344Stuart WeitzmanShoppingClothing
345Sur La TableShoppingClothing
346TargetShoppingClothing
347The North FaceShoppingClothing
348TibiShoppingClothing
349TimberlandShoppingClothing
350UGGShoppingClothing
351Under ArmourShoppingClothing
352Urban OutfittersShoppingClothing
353VansShoppingClothing
354Vera WangShoppingClothing
355Victoria's SecretShoppingClothing
356West ElmShoppingClothing
357WolverineShoppingClothing
358ZapposShoppingClothing
359Tractor SupplyTools & SuppliesWoodworking, Yard, Mechanic
360Capital OneMisc. SpendingCredit Card Payment
361Tractor SupplyTools & SuppliesWoodworking, Yard, Mechanic
362Habitat RestorePrimary HomeImprovements
Auto Cat Items
Cells with Data Validation
CellAllowCriteria
B3:B362List=DDD_Expense_Categories
C3:C362List=XLOOKUP(B3, DDD_Expense_Categories, DDD_for_List, "", 0)
 
Last edited:
Upvote 0
Example.xlsm
BCDEFGHIJKLMNOPQRSTUVWX
1Dependent Dropdown List of Income & Expense Categories
3Income: Client 1Income: Client 2Insurance: Client 1Insurance: Client 2EmergencyMisc. SpendingPrimary HomeRental PropertyVacation HomeInsuranceAutomotiveEntertainmentFoodCell PhonesChild ExpensesGivingPetSelfcareShoppingRecreational VehiclesStorageTools & SuppliesInvestment Contributions
4SalarySalaryUnknownUnknownUnknownUnknownUnknownRental IncomeUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknownUnknown401K
5Bonus Bonus HealthHealthUnexpected SpendingLine of CreditMortgageUnknownMortgageCollectibles: Artwork, Coins, Etc. Car LoanConcerts, Theater, Etc.GroceriesCell PhoneChild Expense AssistanceBirthdays, Anniversaries, Etc.Food, Grooming, BoardingEducation (All)AmazonATV, Golf Cart, Etc.Storage Unit RentalWoodworking, Yard, MechanicRoth 401K
6Social SecuritySocial SecurityDentalDentalCredit Card PaymentEscrowMortgageEscrowPet PlanLeaseElectronicsFast FoodDaycareCharity, Church, Etc..Vet VisitsGrooming: Haircuts, Nails, Etc.ClothingBoatingIRA
7Pension Pension VisionVisionRentEscrowHome Equity LineFuel/Tolls (All)Hobbies: Golfing, Sports, Etc.DanceGym Memberships (All)Other MerchandiseFuel, Maintenance, Etc.Roth IRA
8Deferred Salary Deferred Salary AD&DAD&DHome Equity LineHome Equity LineCable, Internet, Landline Insurance (All)Netflix, Amazon Prime, Audible, Etc.PreschoolHealth Care OOP / PrescriptionBrokerage
9Distribution from Business Distribution from Business LifeLifeCable, Internet, LandlineElectricCleaning Maintenance (All)RestaurantsSchoolOrthodonticsPersonal Savings
10Capital Gains (1231) Capital Gains (1231) HSA ContributionsHSA ContributionsCleaningHOA FeesDecorations Registrations (All)Vacation
11Other Income (1045 Gain) Other Income (1045 Gain) Health Bridge to MedicareHealth Bridge to MedicareDecorationsImprovementsElectric
12Dividends Dividends MedicareMedicareElectricInsuranceHOA Fees
13Taxable Interest Taxable Interest Medicare Supp & DMedicare Supp & DHOA FeesMowingImprovements
14Rental IncomeRental IncomeDisabilityDisabilityImprovementsNatural GasInsurance
15Long Term CareLong Term CareInsuranceOther ServicesLawncare
16Liability (umbrella)Liability (umbrella)LawncareProperty TaxesMowing
17Supp. LifeSupp. LifeMowingRepairs and MaintenanceNatural Gas
18Survivor Benefit PlanSurvivor Benefit PlanNatural GasTrashOther Services
19Other ServicesWaterProperty Taxes
20Property TaxesRepairs and Maintenance
21Repairs and MaintenanceTrash
22TrashWater
23Water
Expense Categories
 
Upvote 0
Above is my dependent drop down list categories. I use the headers as =DDD_Expense_Categories as combobox1 and =DDD_for_List for combobox2 which is this is the formula for DDD_for_List
=OFFSET('Expense Categories'!$B$4, 0, 0, MAX(DDD_Numbers), COUNTA(DDD_Expense_Categories))

The goal of the form is to populate the table Unique Recurring Table above
 
Upvote 0
If the named range "DDD_Expense_Categories" corresponds to the data in row 3 of sheet "Expense Categories", and you are using VBA to find the data for each category, then the named ranges for each category are not necessary and the code is simpler:

VBA Code:
Private Sub UserForm_Initialize()
  Dim category As Range
 
  ' Populate ComboBox1 with items from the DDD_Expense_Categories named range
  For Each category In ThisWorkbook.Sheets("Expense Categories").Range("DDD_Expense_Categories")
    If category.Value <> "" Then
      ComboBox1.AddItem category.Value
    End If
  Next category
End Sub

VBA Code:
Private Sub ComboBox1_Change()
  Dim ws As Worksheet
  Dim cell As Range, f As Range
  
  Set ws = ThisWorkbook.Sheets("Expense Categories")  ' Set the worksheet with categories
  ComboBox2.Clear                                     ' Clear ComboBox2 before populating it
  
  If ComboBox1.ListIndex = -1 Then Exit Sub
  Set f = ws.Rows(3).Find(ComboBox1.Value, , xlValues, xlWhole, , , False)    ' Search ComboBox1
  If f Is Nothing Then
    MsgBox "Category does not exist"
  Else
    For Each cell In ws.Range(f.Offset(1), ws.Cells(Rows.Count, f.Column).End(3)) ' Add each value from the range to ComboBox2
      If cell.Value <> "" Then
        ComboBox2.AddItem cell.Value
      End If
    Next cell
  End If
End Sub

🧙‍♂️
 
Upvote 0
Solution

Forum statistics

Threads
1,224,596
Messages
6,179,802
Members
452,943
Latest member
Newbie4296

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