Show Excel Scenarios With Excel VBA

Previously, we looked at using Excel Scenarios to compare high, low and medium budgets, all in the same worksheet cells.

To make Excel Scenarios easier to use, you can add a bit of Excel Scenario programming.

Make a List of Scenarios

First, create a list of scenario names, using the ScenarioList code shown below.

Next, add a data validation drop down list, so users can select one of the scenarios.

Excel Scenario Selection Code

Add the following code to the worksheet module, to change the scenario, when a selection is made in the data validation drop down list.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If ActiveSheet.Name = Me.Name Then
  If Target.Address = Range("Dept").Address 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

Create a List of Excel Scenarios

To automatically create a list of scenarios, to use in the data validation drop down list, you can use Excel VBA.

This procedure creates a list of scenarios from the Budget worksheet, and 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
With wsLists
  .Range(.Cells(1, 1), .Cells(iRow - 1, 1)) _
  .Sort Key1:=.Cells(1, 1), _
      Order1:=xlAscending, Header:=xlYes
End With
End Sub

More Excel Scenario Programming

Visit the Contextures website for more examples of Excel Scenario programming.

For example, if you want users to add more scenarios, turn off the error alert in the data validation cell.

Then, add a worksheet button that they can click, to add new scenarios.

___________________