Contextures

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 Show  

scenarios

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 Scenario.

Note: This example will only work in Excel 2000, or later versions.

Create a list of Scenarios

  1. On the Insert menu, choose Worksheet
  2. Name the new sheet as Lists  
  3. Starting in cell A1, type the heading, and Scenario names, as shown below.  go to top

list of scenarios

Name the list of Scenarios

The list will be easier to maintain if it's a dynamic named range.
To create the named range:

  1. On the Lists worksheet, select cell A1
  2. Choose Insert | Name | Define
  3. For Names in Workbook, type: ScenarioList
  4. For Refers to, type the following formula:

=OFFSET(Lists!$A$1,1,0,COUNTA(Lists!$A:$A)-1,1)

  5. Click OK

define a name

Create the dropdown list

  1. On the Budget worksheet, select cell B1
  2. Choose Data | Validation
  3. From the Allow dropdown, choose List
  4. In the Source box, type:    =ScenarioList 
  5. Click OK go to top    

create a list of scenarios

Add the Event Code

  1. Right-click on the Budget sheet tab
  2. Choose View Code
  3. From the Object dropdown, at the top left, choose Worksheet.

    select Worksheet

  4. From the Procedure dropdown, at the top right, choose Change.

    choose the Change procedure

  5. Where the cursor is flashing, enter the following code:
If Target.Address = "$B$1" Then
    ActiveSheet.Scenarios(Target.Value).Show
  End If
  1. Choose File | Close and Return to Microsoft Excel 

Test the Code

  1. On the Budget worksheet, select an item from the dropdown list in cell B1.
  2. That Scenario will be displayed.
  3. Close and save the workbook.

select a scenario name

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 completed file  go to top

More Tutorials

Create and Show Scenarios

Scenario Summaries

Macros for Excel Scenarios

Search Contextures Sites

 

Excel Tools Add-in

 

 

Excel Data Entry Popup List

 

 

 

Last updated: March 8, 2017 10:42 PM
Contextures RSS Feed