Excel -- Scenarios -- Programming
Create a List of Scenarios   Create a New Scenario   Test the Code

You can use Scenarios to store several versions of the data in a worksheet. To manage them, you can use programming to display, list, and create Scenarios. This example will automate the Scenarios created here:    Scenarios -- Automatically Show    Download the zipped sample starter file

Create a List of Scenarios To list all the Scenarios on a worksheet, you can adapt the following code. This example lists all the Scenarios from the Budget worksheet, starting the list in cell A2 on the Lists worksheet. It then sorts the list alphabetically. Sub ScenarioList() Dim sc As Scenario Dim wsBudget As Worksheet Dim wsLists As Worksheet Dim iRow As Integer iRow = 2 'leave row 1 for heading Set wsBudget = Worksheets("Budget") Set wsLists = Worksheets("Lists") wsLists.Columns(1).ClearContents wsLists.Cells(1, 1).Value = "Scenarios" For Each sc In wsBudget.Scenarios wsLists.Cells(iRow, 1).Value = sc.Name iRow = iRow + 1 Next sc wsLists.Range(Cells(1, 1), Cells(iRow - 1, 1)) _ .Sort Key1:=wsLists.Cells(1, 1), _ Order1:=xlAscending, Header:=xlYes End Sub

Create a New Scenario The sample workbook has Scenarios for the Finance and the Marketing budgets. In this example, a Scenario for the Sales department will be added. Disable Data Validation error alert On the Budget worksheet, cell B1 has a data validation dropdown list. To allow other items to be entered in the cell: Select cell B1 on the Budget worksheet Choose Data | Validation On the Error Alert tab, remove the check mark from Show error alert after invalid data is entered. Click OK. Enter the data on the worksheet Enter the revised budget data shown below: A B 1 Budget 2006 Sales 2     3 Sales $ 4,100,000 4 Expenses $ 2,450,000 5     6 Profit =Sales - Expenses

Add Error Handling to the Event Code The Budget worksheet has event code that displays a Scenario when a Scenario name is selected in cell B1. The following revised code will handle errors if a new Scenario name is entered in cell B1. Right-click the Budget worksheet tab, and modify the Worksheet_Change code, as shown below: Sub CreateScenario() Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo errHandler If Target.Address = "$B$1" Then ActiveSheet.Scenarios(Target.Value).Show End If Exit Sub errHandler: If Err.Number = 1004 Then MsgBox "That Scenario is not available" Else MsgBox Err.Number & ": " & Err.Description End If End Sub Choose File | Close and Return to Microsoft Excel.

Add the CreateScenario code To create a new Scenario on a worksheet, you can adapt the following code. This example creates a Scenario from the data entered on the Budget worksheet, and uses the department name as the Scenario name. Sub CreateScenario() On Error GoTo errHandler Dim wsBudget As Worksheet Dim wsLists As Worksheet Dim rngChange As Range Dim strName As String Dim sc As Scenario Dim lRow As Integer Set wsBudget = Worksheets("Budget") Set wsLists = Worksheets("Lists") Set rngChange = wsBudget.Range("Dept,Sales,Expenses") strName = wsBudget.Range("Dept").Value 'create the scenario wsBudget.Scenarios.Add Name:=strName, _ ChangingCells:=rngChange 'find the first available row lRow = wsLists.Cells(Rows.Count, 1).End(xlUp).Row + 1 wsLists.Cells(lRow, 1).Value = strName 'sort the list With wsLists .Range(.Cells(1, 1), .Cells(lRow, 1)).Sort _ Key1:=.Cells(1, 1), _ Order1:=xlAscending, Header:=xlYes End With Exit Sub errHandler: If Err.Number = 1004 Then MsgBox "The Scenario could not be created." _ & vbCrLf & "Please try a different name." Else MsgBox Err.Number & ": " & Err.Description End If End Sub

Test the Code From the Tools menu, choose Macro | Macros In the list of macros, select CreateScenario Click the Run button. 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
		The Excel Store	Debra Dalgleish