Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Custom Commandbar in Template loses its assigned macro...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help...

    Several of the controls on a custom command bar have macros assigned to them...
    When I re-open this template after having saved a copy as a workbook, the commandbar's control's, which have assigned macros, now refer to the saved workbook and not the template.
    I'm losing hair on this one.
    Thanks for the replies...
    Tom

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom, I always try to create commandbars on the fly, that is, create them on the Workbook_Open and delete them on Workbook_BeforeClose.

    I saw a while ago a suggestion that I think can work for you, in the Workbook_Open they made sure that each button pointed to the Active Workbook, not the original Workbook. I imagine that this is done by setting the OnAction property of each button.

    Hope that helps
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'll try it out
    Thanks Juan.
    Tom

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juan, you are a genius!



    Option Explicit

    Private Sub Workbook_Open()
    Dim MyCustomBar, MyCustomBarControl
    Set MyCustomBar = Application.CommandBars("SOSE Outcomes")
    Application.CommandBars("SOSE Outcomes").Enabled = True
    For Each MyCustomBarControl In MyCustomBar.Controls
    Select Case MyCustomBarControl.Caption
    Case "Paste to My Class"
    MyCustomBarControl.OnAction = "SOSECutPaste"
    Case "4 outcomes per page"
    MyCustomBarControl.OnAction = "RH90"
    Case "3 outcomes per page"
    MyCustomBarControl.OnAction = "RH120"
    End Select
    Next
    Application.CommandBars("SOSE Outcomes").Visible = True
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("SOSE Outcomes").Visible = False
    Application.CommandBars("SOSE Outcomes").Enabled = False
    End Sub



    Works perfectly!
    This is actually for Chookers.
    I will let him/her know.
    Thanks!
    Tom

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great ! I would still prefer to create that commandbar on the fly !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mr T thank you so much for that, yes I will thank Juan for sure. Will try it out as soon as I get out of class. I did actually read Ivan's reply before BUT I just didn't know enough to understand it! I am slowly getting there, sorry Ivan.

    Now for a really stupid question. For me to create the custom bars on the fly, what exactly does that mean? If anyone can clarify I will certainly do that, and in any case I'd love to know.

    Mr. T thanks so much, and Ivan and Juan!!!
    Cheers!

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chookers, I have never created a custom bar with code, but I have seen plenty of reference to it in VBA help and at Microsofts MSDN site. Start there and then post if you need help with the finer points.
    Tom

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 15:46, chookers wrote:
    Mr T thank you so much for that, yes I will thank Juan for sure. Will try it out as soon as I get out of class. I did actually read Ivan's reply before BUT I just didn't know enough to understand it! I am slowly getting there, sorry Ivan.

    Now for a really stupid question. For me to create the custom bars on the fly, what exactly does that mean? If anyone can clarify I will certainly do that, and in any case I'd love to know.

    Mr. T thanks so much, and Ivan and Juan!!!
    Cheers!
    Hi Chookers if theres something you don't
    understand then just ask, someone will always
    try and reply/help no matter what the Q.

    Creating a commandbar on the fly just means
    CREATING your custom commandbar when your
    workbook opens. When your workbook is closed
    it gets DELETED, not hidden. If it is just
    hidden/not visible then you can potentially
    still have a reference to this book that could be called up again.

    There are plenty of examples on the Net, and
    I have used this a bit.

    Have a look here for the MenuMk

    http://www.j-walk.com/ss/excel



    Examples: Have a look @ this;


    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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