Custom Commandbar in Template loses its assigned macro...

[Legacy 98055]

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

 

[Legacy 98055]

I'll try it out
Thanks Juan.
Tom

 

[Legacy 98055]

Juan, you are a genius!

<pre>

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

</pre>

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!

 

[Legacy 98055]

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;