You are here:

Excel/Userform Cascading Combo-boxes


Using Excel 2003 or Excel 2007/2010 in Compatibility mode


When coding I try to use the same Userform for multiple purposes to minimize the size of the VBA project.

If the userform has 2 cascaded comboboxes I can populate combobox1 when I call the form:

with Form XYZ
for i= 1 to 5
.combobox1.additem entry(i)
next i
end with

In the userform coding I use the combobox1_change event to enable and populate combobox2. Since the userform is multi-purpose I use Select Case Me.caption to populate combobox2 with different things depending on which reason I am using the form but I can end up with a lot of different Cases in the combobox1_change procedure.

Is there a way for me to define the combobox1_change action in the procedure that called the userform so I will not need to list all of the cases in the userform combobox1_change procedure?



Is it possible to  write code with code - yes.  Do you want to do that?  I would probably suggest not.  Having code that modifies code triggers virus detectors from what I have heard.  

Chip Pearson has documented most of what you would need to know.

I don't think he specifically addresses the class module associated with the userform, but I would expect you can use the methods he shows to do what you want.

However, it seems like you are just moving a much bigger set of code to your procedure outside the userform class module.  I don't know what is going on in the code associated with each case statement, but it seems like you could come up with clever procedure names and pass in an array with the procedure name linked to each of the combobox listindex values.  Or something similar.   You can use execute a variable procedure name.

======= Added information ===================

I forgot to give you a second example.  John Walkenbach has documented how to create a userform programmatically.  This may be the next step you want to go to so the userform doesn't even exist - create it when you need it and when the code is done, the userform goes away.

It also gives you some insight on how to address the userform class module even if you want to pursue just modifying the change event.  

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.

[an error occurred while processing this directive]