Editing custom Excel toolbar?? 1

[sjh]

Hi,

I am updating an excel application that someone else developed. He created a custom menu/toolbar which gets displayed as the application starts (the standard menu gets hidden).

I am trying to add a new menu item to this custom toolbar. I go to Tools -> Customize -> and then select the custom toolbar. The custom toolbar is displayed. I add a new menu item (link a macro, etc), save the excel application. However, when I exit from the Excel app and restart it again, the new menu item that I just added disappears! I searched the VBA code to see if he is creating the menu items programmatically, but I don't see anything like that.

If anyone can help me out on this one, I would appreciate it very much!

Thank you,
Susie

 

[rmikesmith]

Susie,

My guess is that the previous developer created the custom toolbar then attached it to the workbook. You can determine this by opening the workbook in question and selecting Tools|Customize (or right-clicking in the toolbar area and selecting Customize...) then clicking the Attach... button. In the Attached Toolbars dialog box, look to see if the custom toolbar name appears in the Toolbars in workbook listbox. If so, then cancel out and follow these steps:
[ol]
[li]Make menu item additions to the custom toolbar as before.[/li]
[li]Open the Attach Toolbars dialog box.[/li]
[li]Select the custom toolbar name in the Toolbars in Workbook listbox.[/li]
[li]Click the Delete button.[/li]
[li]Select the custom toolbar name in the Custom Toolbars listbox.[/li]
[li]Click the Copy button.[/li]
[li]Click the OK button.[/li]
[li]Click the Close button.[/li]
[li]Save the workbook.[/li]
[/ol]
Re-open the workbook to test.

Note: The Copy & Delete buttons in the Attach Toolbars dialog box are the same physical button; only the caption changes.

Hope this helps.
Mike

 

[sjh]

Hi Mike,

Thank you so much for your post! I really appreciate it.

My custom toolbar name doesn't appear in the Attached Toolbars dialog box at all. It's not in the Toolbars in Workbook listbox nor in the Custom Toolbars listbox.

I ended up adding menu items programmatically in the Workbook Open event, but if you have more tips on the topic, please let me know!

Thank you again,
Susie

 

[rmikesmith]

Hi Susie,

Further observations:

I believe the consensus among Excel developers regarding custom menubars/toolbars is that it is better to create and delete these programmatically. It's the approach I use in my own applications.

Based on your statement that you saw no code creating the custom menubar, I guessed it was attached to the workbook. Since that isn't the case, perhaps the code resides in an add-in.

Another advantage to creating a custom menubar in code is it is easier to handle substituting your custom menubar for the standard Worksheet Menubar. Creating your commandbar with Menubar = True does the trick, as there can be only one menubar visible at a time. Deleting the custom menubar restores Excel's.

Sounds like you've solved your problem. Post back if there are any other issues.

Mike

 

[sjh]

Hi Mike,

Thank you for your helpful post and advice!

Is there a way to access the Excel add-in? I've never dealt with add-in's before.

Thank you,
Susie

 

[PHV]

When in VBE (Alt+F11) take a look at the Project browser (Ctrl+R).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244