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.

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:

Select cell B1 on the Budget worksheet

On the Ribbon's Data tab, click 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:

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.

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

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.