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