Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scenarios - auto (vba?) (Excel97SR2)

    I have developed a spreadsheet with scenarios and would like to email it to neophytes. I know how to make a Command Button and hook it to a macro.

    I don't know how to write the EXCEL/VBA code to cause the scenario box to pop up on command.

    This is bugging me, because I had considered my VBA skills to be near-average, but the Excel97 help files give me little clue as to what to do.

    The best I can do so far is to record an Exel97 macro which selects a specific scenario. I could write code to obtain all the scenrios and cycle through them, but I really want the user to be presented with the dialogue box listing the scenarios, so that they can start to explore, and maybe add scenrios of their own.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Is this what you are looking for:

    <pre> Application.Dialogs(xlDialogScenarioAdd).Show
    </pre>

    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Legare,

    I think <pre>Application.Dialogs(xlDialogScenarioCells).Sh ow </pre>

    might be better if the workbook already contains scenarios, because you have the option of Selecting, Adding or Editing

    Andrew C

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    > Is this what you are looking for:

    No. And Yes. (That's "No, Thank You" and "Yes, Thank You"!)

    As Andrew has pointed out, it's the cells that does the trick; I found that out before seeing Andrew's post(Thanks Andrew!) by standing on your shoulders and threreby being able to see the built-in dialog constants.

    'Twas but the work of minutes to try each one, to see if there was a way of doing it.

    So yes, your prompting was what I needed to get me over the hurdle.

    And speaking of minutes, your 45 minutes for a response isn't bad either!
    <pre>Sub test()
    'Application.Dialogs(xlDialogScenarioAdd).Show
    Application.Dialogs(xlDialogScenarioCells).Show
    End Sub
    </pre>


  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Run-time error '1004' Show method of Dialog class failed.


    I get this when I am using a command button, but not when I assign the macro to a toolbar.

    My macro is "Test" as posted previously.

    I assign this macro to the toolbar.

    I call the SUB TEST from the Private Sub cmdScenario_Click().


    Not important right now. Just for information.

    Here is the entire contents of the code module associated with 'Sheet1"

    <pre>Option Explicit

    Private Sub cmdScenario_Click()
    Call test
    End Sub
    Public Sub test()
    'Application.Dialogs(xlDialogScenarioAdd).Show
    Application.Dialogs(xlDialogScenarioCells).Show
    End Sub
    </pre>


  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Chris: It works fine for me for a button on a user form. However, it does give that error if I try to use it from a button created from the Control Tool Box on a worksheet. I can't find a way to get it to work in that case. I was able to get it to work by using a button from the Forms Tool Bar and putting that button on the worksheet.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Oh great. Just GREAT! Until lunchtime I didn't know about the Forms toolbar. Here's something else to cram into my brain (grin!).

    I now have a button on my sheet, which is just what I wanted, thank you!

    How odd that it can be done via Forms buttons but not by Toolbox buttons.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Se La Excel
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Chris,

    I suspect that this is just of academic interest, but the problem seems to have been rectified ib XL2000, i.e. your procedures would work as expected.

    I think if you used Office 95 you would have encountered the Forms Toolbar, but am open to correction on this.

    Andrew

  10. #10
    New Lounger
    Join Date
    Mar 2001
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scenarios - auto (vba?) (Excel97SR2)

    Guys,

    The problem (microsoft feature?) is that by default, a commandbutton or other activex control on a sheet takes the focus away from the sheet. You'll notice most toolbar buttons grey out when the command button is clicked.

    You can get around it though...

    In the properties for the command button there is a "Take focus on Click" property, which can be set to False.

    Your code should then work OK from the command button.

    pt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •