Cannot Remove Macro Icon From Tool Bar

[Gordon Wilmot]

Hi,
I am using Windows XP and Office 2000.
I have just completed an exercise which required putting
an icon on the tool bar and an item in the tools menu to
operate a macro.

I now want remove the icon from the tool bar and the item
from the Tools menu. So far I have been unsuccessful.

Although it was stipulated via the drop down box that the
macro was only to be used in the work book in which it
was generated, the icon and the item in the Tools menu
appear in all my work books.

I have tried un-installing Office and re-installing, but
the icon and menu item still remain.

Hope you can help

Gordon Wilmot

PS I could not find my earlier message.

 

[Jan Karel Pieterse]

Hi,

Excel's toolbar and menu customisations are Application-
wide by default. If you want a certain menu-item or
toolbar button to be unavailable whilst certain workbooks
are visible (or only available whilst one workbook is
there), you need to use VBA code to show and hide them.

To remove an entry (or a button) from a commandbar, simply
drag it off the bar after selecting "Customise" from the
rightclick menu.

Here is some more general info on toolbars:

Excel keeps toolbar and menubar customizations in a file
with the extension .xlb. The exact filename depends on
Excel version and install, but usually is: Excel9.xlb or
Excel.xlb or Username8.xlb.
Often this file can be found in your WINDOWS directory.

You can attach a toolbar to a workbook. When this workbook
is loaded, XL checks if the toolbar is on the system. If
not, it copies the toolbar from the workbook to the system.

After creating *or changing* the toolbar, you should
attach the toolbar to your workbook:

- activate the workbook to which you want to attach the
toolbar
- Rightclick the toolbar, select 'customize'
- Click 'Attach' (Toolbars Tab)
- If the workbook already contains a toolbar by that name,
delete it first by clicking on it on the righthand side
and choosing Delete.
- Select your toolbar (on the left) and press 'copy'
- Save the workbook (optionally: save_as an add-in).

Also, you should include code that deletes the toolbar
when your workbook or add-in is closed, so that when you
deliver a new version of your workbook the new toolbar
will be used i.s.o the old one. You can do that in the
Thisworkbook module, using the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
On Error Resume Next 'In case Toolbar is absent
Application.CommandBars("YourBarsName").Delete
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Dave Peterson]

How about Tools|Customize (just to show the dialog.)
click on Tools to expand the menu
find your added option and drag it off the toolbar (until it has a big X near
it).

Do the same with the icon you added to the existing toolbar.

======
My bet is your *.xlb file is hidden and some versions of windows have to be told
to look for hidden files (and hidden folders).

 

[Gordon Wilmot]

Hi Gord,
Stopped my folders being hidden and searched again for
*.xlb, but no luck.

As far as I know, Excel does not create an xlb file until
it is required, an so far I have not had cause to use it.

Thanks all the same.
Gordon