Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Peterborough, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding & removing buttons via VBA (2000 / SR1)

    I know this is a newbie question, but I can't find the answer anywhere and can't remember the answer either although I'm sure I knew it once upon a time......

    I have an Excel template in which I want to use a button to run a macro that I've written.

    I don't want the button to be on the toolbar unless this specific template is in use. How do I add the button when a sheet based on the template is created or opened and removed when it is closed or switched away from?

    Pointers to comprehensible code will be most welcome :-)

    Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Adding & removing buttons via VBA (2000 / SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Ian

    try and have something in the worksheet events that display the button and when the sheet is deactivated it will dismiss the button. But you need this button to be on a toolbar by itself just to make things easier.

    I'll write the code in a minute.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding & removing buttons via VBA (2000 / SR1)

    Have a look at this thread.

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Adding & removing buttons via VBA (2000 / SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> again Ian

    This workbook has the code in the following areas:

    1) Sheet 1 in Worksheet_Activate and Worksheet_Deactivate.
    2) Thisworkbook in Workbook_Activate and Workbook_Deactivate

    So when you deactivate the worksheet Sheet1, the Worksheet_Deactivate will trigger and it will hide the toolbar called "My Custom Toolbar 1"

    When you activate the worksheet Sheet1the Worksheet_Activate will trigger and show the toolbar.

    Now if you deactivate the workbook but say switching to another workbook or opening another workbook this will deactivate the worksheet but the Worksheet_Deactivate may not trigger so the Workbook_Deactivate will trigger in this case, and that also does the hiding.

    When you reactivate the workbook, you need to know if you are activiating the workbook with Sheet1 being active so that is what the Workbook_Activate will do.

    Test it and tell me if you like it.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Peterborough, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks guys....

    A combination of both your suggestions has got a very workable solution.

    Thanks again.

Posting Permissions

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