Automating the creation of a scenario

Posted by Elizabeth on July 11, 2000 12:44 PM

Is there a way to create a macro to automate the set up of a new scenario? When I try to do this the scenario function is disabled.

Posted by Ryan on July 11, 0100 12:49 PM

How do you want to automate it? Are you getting values from somewhere else or do you want to still enter the info? Let us know!

Ryan

Posted by Elizabeth on July 11, 0100 1:53 PM

I want to set up an automated process for someone to get into scenario manager

Posted by Ryan on July 11, 0100 2:02 PM

I still don't know what you want but here is something, let me know if this isn't it, and describe a little more what you want.

Ryan

Sub ShowScenario()
Application.Dialogs(xlDialogScenarioAdd).Show
End Sub

Posted by Elizabeth on July 11, 0100 2:34 PM

That was it! Thanks.

Posted by Tim on July 11, 0100 5:21 PM

OK, you have shown how to invoke the Scenario Add dialog, but how do I invoke the Scenario Manager dialog?

Posted by Tim on July 11, 0100 6:17 PM

Invoking the Scenario Manager Dialog from vba in Excel97

You can invoke the scenario manager dialog (even though there isn't a pre-defined constant with the right dialog number)

I found the right number (305 for Excel97) using the following routine,
sub FindScenarioManagerDialog
Dim dNo As Integer
On Error Resume Next
For dNo = xlDialogScenarioAdd - 10 To xlDialogScenarioAdd + 10
Application.Dialogs(dNo).Show
If MsgBox("You just viewed dialog #" & Str(dNo) & ". Shall I stop showing dialogs", vbYesNo, "Searching for Scenario Manager Dialog") = vbYes Then
Exit Sub
End If
Next
end sub

then I used Ryan's code to get the manager up
Sub ShowScenario()
Application.Dialogs(305).Show
End Sub