Maintaining Macro Toolbar Button Properties

[Gary B]

I am working in Excel 2003.

I have asigned a macro to a customer toolbar button which
is only available when a certain sheet in the workbook is
open. This button is on the standard toobar. I was able
to do this with the Worksheet_Activate and
Worksheet_Deactivate subroutines.

I made took the smiley face button from
View/Toolbars/Commands/Macros and drug it to the standard
toolbar. I did not create my own button.

On worksheet deactivation, the standard toolbar is reset
and the custom macro button goes away. However, each
time I reactivate the sheet and the button is placed back
on the toolbar, I have to reassign the macro to it and
also change the name from "Custom Buttom".

How do I get the custom button to retain its name and the
macro assigned to it?

Thanks,
Gary

 

[Guest]

hi,
I don't know if this has anything to do with it but is the
macro a sheet macro. that is a macro assigned to a sheet
and not the workbook or general.
I have never encountered this problem before. i usually
use button on the sheet to fire the macros.

 

[Gary B]

Yes, it is a sheet macro. The macro for adding the
button is:

Private Sub Worksheet_Activate()
Application.CommandBars("Standard").Controls.Add _
Type:=msoControlButton, ID _
:=2950, Before:=6
End Sub

Nothing fancy, this is straight from recording the macro.

When the sheet is deactivated, I run:

Private Sub Worksheet_Deactivate()
Toolbars("Standard").Reset
End Sub

It seems to me there should be something added to the
Worksheet_Active macro that will automatically assign the
macro to the newly added button and also change the name
of the button from Custom Button to name I want, dist
list. I'm not sure how to do this.

Thanks,
Gary

 

[Rob van Gelder]

It is possible to assign macros to buttons at runtime.

I have CommandBar examples on my website.