problem adding button to an EXISTING dropdown menu on a Worksheet Menu Bar

[Karen P]

I have an EXISTING drop-down list on the Worksheet Menu
Bar.

I want to add an item to this drop-down list.

This list contains macros that are run only in this sheet -
I just want to add another entry to the list and attach a
macro to it. The new macro is already set up.

This drop-down list, at some point in past years, has been
attached to this workbook, so this drop-down list appears
ONLY in this one worksheet - I don't want it available
anywhere else.

Using the Macro category, I've added the button & assigned
the macro. I save the workbook.

When I reopen the workbook, the new entry in the drop-down
list is gone.

Why does it keep disappearing & how do I get it to stay?

Thanks.

 

[Bernie Deitrick]

Karen,

If the toolbar only appears when that one workbook is opened, and
disappears when it is closed, then there is code within the workbook's
Auto_Open macro or workbook open event that creates the toolbar, and
code to remove it. You will need to modify those macros to get your
button to stay.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie, thanks for the info. Essentially the question you asked is true.

How do I access the workbook's Auto_Open macro or determine the open event which causes the toolbar item?

Thanks.

Karen

----- Karen P wrote: -----

I have an EXISTING drop-down list on the Worksheet Menu
Bar.

I want to add an item to this drop-down list.

This list contains macros that are run only in this sheet -
I just want to add another entry to the list and attach a
macro to it. The new macro is already set up.

This drop-down list, at some point in past years, has been
attached to this workbook, so this drop-down list appears
ONLY in this one worksheet - I don't want it available
anywhere else.

Using the Macro category, I've added the button & assigned
the macro. I save the workbook.

When I reopen the workbook, the new entry in the drop-down
list is gone.

Why does it keep disappearing & how do I get it to stay?

Thanks.

 

[Bernie Deitrick]

Karen,

USe Alt-F11 to get into the VBE, then browse to your workbook in the porject
explorer, expand it, and look in any codemodules for Auto_open macros, and
in the Thisworkbook object's codemodule (double click on Thisworkbook in the
object explorer) and see if there is code in the Workbook_open event.

HTH,
Bernie