How populated is Naples?

Userform combo box populated by selction in another combo box

• I currently have two in-cell drop down lists (Category and SubCategory) which are populated using a named range, and the subcategory is populated using the Indirect function in Data validation. I am changing the input method in this sheet to a userform but don't know how to have the secondary combo box populated based on the selection of the first combo box. For example, combo box #1 has category selections of "1", "2", and "3" and and when the user selects "1" then combo box #2 would display 1a, 1b, 1c. Currently the first userform combo box is populated using a named range with the following code: Private Sub UserForm_Initialize() Dim ccategory As Range Dim ws As Worksheet Set ws = Worksheets("Formulas") For Each ccategory In ws.Range("CATEGORY") With Me.ComboBoxcategory .AddItem ccategory.Value .List(.ListCount - 1, 1) = ccategory.Offset(0, 1).Value End With Next ccategory End Sub I had tried to set up the secondary combo box with the following code but its wrong (I have no VBA experience and have mostly just been plugging together pieces of code I find online) Private Sub ComboBoxcategory_Change() Dim ctype As Range Dim ws As Worksheet Set ws = Worksheets("Formulas") For Each ctype In ws.Range("Category") With Me.ComboBoxtype = Application.WorksheetFunction.indirect(ComboBoxcategory.Text) .AddItem ctype.Value .List(.ListCount - 1, 1) = ctype.Offset(0, 1).Value End With Next ctype End Sub What is the best way to have the secondary combo box populated? Is there a way to use the Indirect function in vba for this? I really appreciate any help you can offer. Leigh

• Answer:

  Create new userform. Add a combobox named cboCategory, with the options 1, 2, and 3. Add a combobox named cboSubcategory. The RowSource property for this combobox should be blank. Add this code: Private Sub cboCategory_Change() cboSubcategory.Clear cboSubcategory.AddItem Me.cboCategory.Value & "a" cboSubcategory.AddItem Me.cboCategory.Value & "b" cboSubcategory.AddItem Me.cboCategory.Value & "c" End Sub Now, whenever selection on cboCategory changes, the options on cboSubcategory will change: if Category is 1, Subcategory options will be 1a/1b/1c. Maybe you don't want to fill the Subcategory dropdown like this. Maybe you want to fill it based on a range on antab. If this is the case, then in your question, describe very very clearly and precisely, in plain English, which range you want cboSubcategory to get these options from. (In your original question, you don't explain in plain English what Private Sub ComboBoxcategory_Change() is supposed to do.)