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