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
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 application.run 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.
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