Contextures Blog

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.

___________________

On the Contextures YouTube channel, someone asked if Excel can automatically create a sheet when the file opens:

• “this question from my friend, she always need to open same file and create a new sheet with the name of the month when her is updating the data. is there any ways to skip the above process?”

Video Answer

Since the question came from YouTube, I provided the answer in a video, which you can see at the end of this blog post.

If you prefer to see the code, or download a sample file, you can find the details below.

How the Add Sheet Code Works

The goal is to add a worksheet with the month name, when the Excel file opens. We only want that to happen at the start of the month, if the sheet doesn’t exist already.

We’ll write a macro that:

• figures out what the current month is
• checks for a sheet with that name
• adds that sheet, if it doesn’t exist

The Add Worksheet Code

Insert a regular module in the workbook, and paste in the following code. I used yyyy-mm as the sheet name format, but you could use a different format.

For example, to see the full month name, use mmmm as the format.

Sub AddMonthWkst()
Dim ws As Worksheet
Dim strName As String
Dim bCheck As Boolean
On Error Resume Next
strName = Format(Date, "yyyy_mm")
bCheck = Len(Sheets(strName).Name) > 0
If bCheck = False Then
  Set ws = Worksheets.Add(Before:=Sheets(1))
  ws.Name = strName
End If
End Sub

Run the Code Automatically

To make the code run automatically, when the workbook opens, you’ll create a Workbook_Open event.

Paste the following code on the ThisWorksheet module:

Private Sub Workbook_Open()
    AddMonthWkst
End Sub

Download the Sample File

To see the workbook and the Add Worksheet code, you can download the Add Worksheet sample file.

The file is in Excel 2007 format, and zipped. It contains macros, so you’ll need to enable them, to see the code working.

Watch the Add Worksheet Automatically Video

To see the steps for creating the code, and making it run automatically, you can watch this Excel Video Tutorial.

_____________