I have an Excel file with a number of defined Scenarios. I would like to add either a VBA button or the regular macro-based button that will call up the Scenarios dialog box or run a particular Scenario.
Advice?
Thanks in advance.
I have an Excel file with a number of defined Scenarios. I would like to add either a VBA button or the regular macro-based button that will call up the Scenarios dialog box or run a particular Scenario.
Advice?
Thanks in advance.
suggestion
I suggest you create a UserForm with some kind of selection method for your scenarios. If your scenarios are dynamic, you should use a ListBox and populate it dynamically.
If you have a fixed set of scenarios, and a large amount, use a DropDownList.
If you have a fixed set of scenarios, and a small amount, use CheckBox or RadioButton selection methods.
If you need help with the code part of this, let us know.
The Form can be created quite easily by using drag&Drop interface in VB.
Jong,
Thanks for the quick feedback. Creating the userform with the scenario selection isn't so much the problem as the code to get the scenario pulled up.
If you could provide some insight, it would be very much appreciated. (but, be aware that my experience with VBA is primarily through the trial-and-error method).
Thanks,
Curtis.
Forms
The only "easy way" to get this to work is to get someone to do it for you
When you say "Scenario", do you mean this Sub/Functions?
If you have all your scenarios devided in their seperate functions, I could paste some code to show you an example.
By Scenarios I am referring to the option in Excel under the Tools menu.
ahh
Iv'e never worked with scenarios before.
Hopefully someone will be able to inform us on how to invoke a scenario from VBA.
Hi CFrazier,
I know next to nothing about Scenario's but maybe this bit of code will help point you in a suitable direction. It will print the scenario names to the immediate window and then display the dialogs.
Sub x()
Dim objScen As Scenario
For Each objScen In ActiveSheet.Scenarios
With objScen
Debug.Print .Name
End With
Next
Application.Dialogs(xlDialogScenarioAdd).Show
Application.Dialogs(xlDialogScenarioCells).Show
Application.Dialogs(xlDialogScenarioMerge).Show
Application.Dialogs(xlDialogScenarioSummary).Show
End Sub
Display More
Re: VBA (or a macro) to call up a Scenario (or the menu of scenarios)
here is a code that I was given to populate a combobox with my scenarios as well as other things... sorry I am not good with code but maybe you can disect this to what you need
Private Sub WORKBOOK_OPEN()
ARBUTUS.show
Dim i As Long
Application.ScreenUpdating = False
If ActiveWorkbook.Name <> "estimate.xls" Then
Exit Sub
Else: With Sheets("flat estimates")
.Activate
With .ComboBox1
.Clear
.AddItem "--Choose Estimate Type--"
End With
For i = 1 To Sheets("flat estimates").Scenarios.Count
Sheets("flat estimates").ComboBox1.AddItem Sheets("flat estimates").Scenarios(i).Name
Next
.ComboBox1.ListIndex = 0
End With
If ActiveWorkbook.Name <> "estimate.xls" Then
Exit Sub
Else: Sheets("flat estimates").Range("B10:B21").ClearContents
End If
End If
Application.ScreenUpdating = True
End Sub
Display More
Don’t have an account yet? Register yourself now and be a part of our community!