Contextures

Macros for Excel Scenarios

Use these macros to show Excel scenarios, to list all the scenarios, or create new scenarios. Get free workbook with the macros

Introduction

Use these macros to show or list Scenarios that you previously created. Or, use a macro to create a new Scenario.

This example uses the Scenarios created in the tutorial on the Automatically Show Excel Scenarios page

Excel Scenarios

Get a List of Excel Scenarios

To list all the Scenarios on a worksheet, you can adapt the following macro. 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.

list of scenarios

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

With wsLists
.Range(.Cells(1, 1), .Cells(iRow - 1, 1)) _
  .Sort Key1:=.Cells(1, 1), _
  Order1:=xlAscending, Header:=xlYes
End With

End Sub

Create a New Scenario

The sample workbook has Scenarios for the Finance and the Marketing budgets. In this macro, 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, follow these steps to turn off the error alert:

  1. Select cell B1 on the Budget worksheet
  2. On the Ribbon's Data tab, click Data Validation.
  3. On the Error Alert tab, remove the check mark from Show error alert after invalid data is entered.
  4. Click OK.

turn off the error alert

Enter the data on the worksheet

Enter the revised budget data shown below:

Cell B1: Sales

Cell B3: $4,100,000

Cell B4: $ 2,450,000

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.

  1. Right-click the Budget worksheet tab, and modify the Worksheet_Change code, as shown below:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If ActiveSheet.Name = Me.Name Then
  If Target.Address = "$B$1" Then
    ActiveSheet.Scenarios(Target.Value).Show
  End If
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
  1. 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 Create Scenario Macro

  1. On the Ribbon's View tab, click Macros, then click View Macros
  2. In the list of macros, select CreateScenario
  3. Click the Run button.
  4. (optional) Add a button to the worksheet, to run the CreateScenario Macro, as shown below.

button to add new scenario

Note: When opening the workbook, you will have to enable macros in order to use this code.

Download the Sample File

Click this link to get the Excel Scenarios Macros workbook. The zipped file contains macros, so be sure to enable macros when you open the workbook, if you want to test the code.

More Tutorials

Create and Show Scenarios

Scenario Summaries

Automatically Show Scenario

Search Contextures Sites

 

Excel Tools Add-in

 

Free Pivot Table Tools

 

 

Peltier Tech Charts for Excel 3.0

 

 

Last updated: December 29, 2016 11:07 AM
Contextures RSS Feed