Custom Commandbar in Template loses its assigned macro...

[Tom Schreiner]

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

[Juan Pablo González]

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

[Tom Schreiner]

I'll try it out
Thanks Juan.
Tom

[Tom Schreiner]

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

[Juan Pablo González]

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

[chookers]

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!

[Tom Schreiner]

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

[Ivan F Moala]

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;