EXCEL VBA: Combo box to list active workbook sheets
Posted on 2003-03-28
I'm working on a workbook template to be used for project profitability tracking. Users will use one sheet ("Plan Input") to input and update planned costs. Periodically, they will click a button to run a VBA procedure that asks the user for a sheet name ("Proposal," "Worst Case," "Scope Change 1," etc.), copies the Plan Input sheet, applies the name, converts all formulas to values, and protects the new sheet. Other sheets will allow the user to input actual costs and actual billings to the client.
On an Analysis sheet, I have a series of formulas that compare actual costs and billings to plan. I want to allow users to select which version of the plan they want to use for comparison. My thought is to provide a combo box that lists all of the sheets in the active workbook. I would then use the selected value in that combo box to modify the formulas that depend on it.
I have two requests: First, could someone help me with the code to build that combo box? Second, if there's a more elegant approach to this problem, please let me know what you recommend.
I always get such good advice, and add a few new techniques to my skillset, every time I ask this forum for advice. Thanks in advance for your help on this question.