Search Contextures Sites

Excel -- Scenarios -- Automatically Show Scenario
Create a Dropdown List of Scenarios   Add the Event Code   Test the Code

You can use Scenarios to store several versions of the data in a worksheet. To make them easier to use, you can use programming to automatically show the selected Scenario. This example will use the Scenarios created here:    Scenarios -- Create and Show   Download the zipped sample starter file

Create a Dropdown List of Scenarios To view a Scenario, you can choose Tools | Scenarios, then select a Scenario, click the Show button, and close the Scenario Manager dialog box. To make it easier to switch between Scenarios, you can create a dropdown list of Scenarios, and use event code to show the selected Scenario. 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 at right.    Name the list of Scenarios The list will be easier to maintain if it's a dynamic named range. 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:

=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A)-1,1)
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 ActiveSheet.Scenarios(Target.Value).Show End If Choose File | Close and Return to Microsoft Excel

Test the Code On the Budget worksheet, select an item from the dropdown list in cell B1. 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 zipped sample completed file

1. Scenarios -- Create and Show   2. Scenarios -- Scenario Summaries   3. Scenarios -- Automatically Show Scenario   4. Scenarios -- Programming

Home     Excel Tips     Excel Files      Blog    Contact
Privacy Policy		The Excel Store	Debra Dalgleish