Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Run Macro from Toolbar (Excel 97)

    I want to have my macro run from a customised toolbar button in one workbook and NOT all workbooks. I have hunted through my books but cannot seem to find a simple instruction.

    I am accustomed to using Word Custom Toolbars to run macros and have them only available in the document I am working in not the normal.dot. I would have thought that Excel would be the same, but apparently not??

    Kerry

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from Toolbar (Excel 97)

    Hi Kerry

    Excel does operate differently in this respect, since there are no direct equivalents to Word's templates. If you're talking of a custom toolbar, then this article might be of help:
    Hide/Restore Excel Toolbars in Excel VBA. Show/Hide a Custom Toolbar & Remove/Restore Excel's Toolbars.

    If it's a custom button on an existing toolbar, the solution will be a bit more complex.

    Alan

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Run Macro from Toolbar (Excel 97)

    Golly it is complicated isnt it!

    Thanks Alan I will have a fiddle with this info.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from Toolbar (Excel 97)

    It looks fiercer than it is. The crux of it is to display your custom toolbar in Excel, when the user is working on your workbook, by running
    .CommandBars("MyToolbar").Enabled = True
    from the Workbook_Activate() event,

    then to hide your custom toolbar when they switch to some other workbook, by running
    .CommandBars("MyToolbar").Enabled = False
    from the Workbook_Deactivate() event.

    The rest is specific to the actual example they're using. You'll get it with the help of an Aspro or two. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run Macro from Toolbar (Excel 97)

    Hi Kerry,
    I may have a working model that I can post as a sample file that does this. If you need it let me know. It is based on what Alan mentions in his replies!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Macro from Toolbar (Excel 97)

    I have attached a very simple example. The workbook adds a button to the Standard toolbar, and shows/hides it as appropriate. When the workbook is closed, the button is deleted.

    This is the code in the ThisWorkbook module:

    ' Make button visible when workbook is activated
    Private Sub Workbook_Activate()
    On Error Resume Next
    Application.CommandBars("Standard").Controls("My Button").Visible = True
    End Sub

    ' Delete button when workbook is closed
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Standard").Controls("My Button").Delete
    End Sub

    ' Make button invisible when workbook is deactivated
    Private Sub Workbook_Deactivate()
    On Error Resume Next
    Application.CommandBars("Standard").Controls("My Button").Visible = False
    End Sub

    ' Create button when workbook is opened
    Private Sub Workbook_Open()
    With Application.CommandBars("Standard").Controls.Add(m soControlButton)
    .Caption = "My Button"
    .OnAction = "MyMacro"
    .Style = msoButtonIcon
    .FaceId = 123
    End With
    End Sub

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run Macro from Toolbar (Excel 97)

    Hey...I was gonna do that!! - <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    PS: I would have copied beep 3000 times....when Kerry clicks the button the PC will REALLY moan!! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Macro from Toolbar (Excel 97)

    You can still post your example - it may demonstrate a slightly different approach. And it''ll sound different! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Run Macro from Toolbar (Excel 97)

    All suggestions are greatfully received.

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

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