Macro to Show Excel Scenario
You can use Excel Scenarios to store several versions of the data in
a worksheet. To make them easier to use, you can use this macro to
automatically show the selected Scenario
Create a Dropdown List of Excel Scenarios
This example will use the Excel Scenarios created here: Excel Scenarios -- Create and
To view a Scenario, click the What If Analysis command on the Ribbon's Data tab, then click Scenario Manager. To make it easier to switch between Scenarios, you can create a
dropdown list of Scenarios, and use event code to show the selected
Note: This example will only work in Excel 2000, or later versions.
Create a list of Scenarios
- On the Insert menu, choose Worksheet
- Name the new sheet as Lists
- Starting in cell A1, type the heading, and Scenario names,
as shown below.
Name the list of Scenarios
The list will be easier to maintain if it's a dynamic
To create the named range:
- On the Lists worksheet, select cell A1
- Choose Insert | Name | Define
- For Names in Workbook, type: ScenarioList
- For Refers to, type the following formula:
5. Click OK
Create the dropdown list
- On the Budget worksheet, select cell B1
- Choose Data | Validation
- From the Allow dropdown, choose List
- In the Source box, type: =ScenarioList
- Click OK
Add the Event Code
- Right-click on the Budget sheet tab
- Choose View Code
- From the Object dropdown, at the top left, choose Worksheet.
- From the Procedure dropdown, at the top right, choose Change.
- Where the cursor is flashing, enter the following code:
If Target.Address = "$B$1" Then
- Choose File | Close and Return to Microsoft Excel
Test the Code
- On the Budget worksheet, select an item from the dropdown list in
- That Scenario will be displayed.
- Close and save the workbook.
Note: When opening the workbook, you will have to enable macros
in order to use this code.
Download the Sample File
Download the zipped sample Excel Scenarios
Create and Show Scenarios
Macros for Excel Scenarios