locked
Access to current "shown" scenario in a worksheet scenarios collection - VBA. RRS feed

  • Question

  • I have a worksheet with several scenarios set up; I want to be able to navigate thru the scenarios collection to find out which is the current scenario. I want to be able to:

    1) Find out the current scenario and save it

    2) Select a scenario; use the Show command

    3) restore the initial scenario using the show command

    Is there any way to do this?

    Sub ScenarioList()
    Dim sc As Scenario
    Dim ws As Worksheet
    Set ws = Worksheets("FinOverview")
    Dim arf As Boolean
    For Each sc In ws.Scenarios
        if sc.isCurrent then
        MsgBox "Name: " & sc.Name 
        MsgBox sc.Parent.Name
        end if
    Next sc
    End Sub

    Monday, October 28, 2013 3:13 PM

Answers

  • Sub Test()
      Dim S As String
      'Get the current scenario
      S = ActiveScenario
      'Show the scenario manager
      Application.Dialogs(xlDialogScenarioCells).Show
      'If we have a scenario
      If S = "" Then Exit Sub
      'And if it is different
      If S = ActiveScenario Then Exit Sub
      'Should we?
      If MsgBox("Back to the previous scenario?", vbOKCancel) = vbCancel Then Exit Sub
      'Switch back
      ActiveSheet.Scenarios(S).Show
    End Sub

    Private Function ActiveScenario(Optional ByVal WS As Worksheet) As String
      'Returns the name of the active scenario if any
      Dim S As Scenario
      Dim R As Range
      Dim i As Long
      If WS Is Nothing Then Set WS = ActiveSheet
      'Access each scenario
      For Each S In WS.Scenarios
        i = 0
        'Check each changeable cell
        For Each R In S.ChangingCells
          i = i + 1
          'Compare the current value with the scenario value
          If StrComp(R.Value, S.Values(i), vbBinaryCompare) <> 0 Then
            GoTo NextScenario
          End If
        Next
        ActiveScenario = S.Name
        Exit Function
    NextScenario:
      Next
    End Function

    • Marked as answer by LKnYC Wednesday, October 30, 2013 1:34 PM
    Monday, October 28, 2013 4:00 PM

All replies

  • Sub Test()
      Dim S As String
      'Get the current scenario
      S = ActiveScenario
      'Show the scenario manager
      Application.Dialogs(xlDialogScenarioCells).Show
      'If we have a scenario
      If S = "" Then Exit Sub
      'And if it is different
      If S = ActiveScenario Then Exit Sub
      'Should we?
      If MsgBox("Back to the previous scenario?", vbOKCancel) = vbCancel Then Exit Sub
      'Switch back
      ActiveSheet.Scenarios(S).Show
    End Sub

    Private Function ActiveScenario(Optional ByVal WS As Worksheet) As String
      'Returns the name of the active scenario if any
      Dim S As Scenario
      Dim R As Range
      Dim i As Long
      If WS Is Nothing Then Set WS = ActiveSheet
      'Access each scenario
      For Each S In WS.Scenarios
        i = 0
        'Check each changeable cell
        For Each R In S.ChangingCells
          i = i + 1
          'Compare the current value with the scenario value
          If StrComp(R.Value, S.Values(i), vbBinaryCompare) <> 0 Then
            GoTo NextScenario
          End If
        Next
        ActiveScenario = S.Name
        Exit Function
    NextScenario:
      Next
    End Function

    • Marked as answer by LKnYC Wednesday, October 30, 2013 1:34 PM
    Monday, October 28, 2013 4:00 PM
  • Hello Andreas!

    Thanks for your answer; I was hoping to avoid iterating thru the Changing Cells value; but, too bad that ActiveWorksheet.ActiveScenario does not exist...

    Marking this as answered.

    Wednesday, October 30, 2013 1:34 PM
  • Hello Andreas!

    Thanks for your answer; I was hoping to avoid iterating thru the Changing Cells value; but, too bad that ActiveWorksheet.ActiveScenario does not exist...

    Marking this as answered.

    If all scenarios are currently have exact same value, you can not tell which one is the active one.

    .....


    Friday, September 5, 2014 5:36 PM