Can't remove old excel toolbars

[nodrog77]

Note: Excel 2003, OS Windows XP (or Windows Vista)

Hey There,
I have a couple of old excel toolbars which I can't seem to get rid of (and neither can my users):

Both the code below and manual edits work fine in the current excel session but then if I exit excel and start again, Hey presto ! they're back again.

'-----------------------------------------------
Public Sub DeleteOldToolbars()

On Error Resume Next
Application.CommandBars("Admin_Supps").Delete
On Error Resume Next
Application.CommandBars("Valuers_Supps").Delete
Err.Clear

End Sub
'------------------------------------------------

Is there any way of cleaning this up ( without reinstalling excel)

Thanks,
Lea

 

[Gavona]

If you are exiting properly then that should not happen. On closing excel a *.xlb file is saved storing a number of options found in Tools, Options plus the current state of your toolbars. Try renaming / deleting the xlb file. Does that resolve the problem?

Gavin

 

[nodrog77]

Thanks Gavin,
I'm not sure what .xlb you are referring to?
Where would it be stored and can I access it using VBA?
Is removing/deleting it going to effect any of the other settings of the user (in which case I might as well just get IT to re-install excel)

I tried saving personal.xls explicitly before exiting but that didn't work either and I have no problem new creating toolbars - just getting rid of the old ones.

 

[combo]

The toolbar can be stored in one of excel files too. When you customise toolbars there is an option (on the dialog) to add custom toolbar to active workbook. Such workbook, while opening, adds the toolbar, if not already present, to excel toolbars collection.
Find a file that adds it and delete the toolbar from the workbook (in the same way as it was added), save changes. Next delete the toolbar from toolbars collection.
The toolbar can be stored in an add-in too. To find it in this case, uninstall all add-ins, delete toolbar. Install add-ins one by one and see if the toolbar is added. The only way to change the add-in is to convert it tp regular workbook, remove the toolbar and save as add-in again.

combo

 

[Gavona]

Use windows search facility. At home mine is in
C:\Users\xxx\AppData\Roaming\Microsoft\Excel\excel11.xlb

If you can create new toolbars. Exit excel. Open excel again and the new toolbars are there. Then probably Combo is correct.

Add-Ins / workbooks can also be added using Tools, References in the VBE (Alt-F11)

Also in the VBE
Do you see any workbooks/Add-Ins in the Project Explorer Window?

Gavin

 

[mintjulep]

The could be getting created again when Excel starts.

Is there a .xlt file in the excel startup directory with code to create them?

 

[Gavona]

You could eliminate a lot of the possibilities by Opening Excel in Safe Mode.

/s, /safemode Forces Excel to bypass all files in the Application Data\Microsoft\Xlstart folder,
the default XLStart folder located in the directory where Excel or Office is installed,
and the alternate startup file location specified on the General tab of the Excel Options dialog box.
It also forces Excel
to bypass the toolbar file (Excel.xlb or
<username>.xlb). You see "Safe Mode" in the
Excel title bar. Use this switch when you want to
start Excel in safe mode.

http://support.microsoft.com/kb/291288

Gavin

 

[nodrog77]

Thanks All,
I was unable to locate any .xlb files on my PC running under Vista - but then again I can't get access "Application Data folders" etc, even when I run in admin mode which is one of the things that make Vista so useless. I don't have admin rights to the other users machines either so fiddling around with .xlb files isn't an option.

None of these Toolbars were added using .xlt etc, they were all configured manually by going to each users machine and selecting tools > customise, new menu etc. Unfortunately they can't be removed that way either.

Looks like they will have to wait until excel is re-installed for the toolbars to finally disappear.

Thanks for trying anyway.

Lea

 

[strongm]

Under Vista and later XLSTART is typically here:

C:\Users\<user name>\AppData\Local\Microsoft\Excel\XLStart

not in

C:\Documents and Settings\<user name>\Application Data\Microsoft\Excel\XLStart

Note that the Documents and Settings 'folder' in Vista is not actually a folder, which is why you don't have access to it (it is a symbolic link, for backwards compatibility, with some fairly restrictive ACLs applied)

 

[Gavona]

Safe mode will avoid loading the xlb files anyway - as a way of testing things out.

If the directory that is storing the xlb file has been write protected then that might cause the issue. Re-installing won't necessarily resolve the issue so worth trying to discover the cause.

I know that in our network installation the xlb file is not in xlstart and does not move with us as we switch pcs (most frustrating in a "hot-desking" environment). Ours (Win XP) is here:
C:\Documents and Settings\UserID\Application Data\Microsoft\Excel

I found it by using the search facility, setting advanced options to enable me to see hidden files and folders, typing C:\ in the Search box (it is hiddenn from me and does not appear in my computer). You can also create a shortcut then edit the shortcut to point at C:\.

Gavin

 

[nodrog77]

OK,

Finally located the .xlb and yes - deleting it resets all the menus back to the defaults. Except those menus - which reappear again!!

have tried accessing and deleting them via the workbook objects, the code now reads:

Public Sub DeleteOldToolbars()
Dim cntrl As CommandBarControl
Dim cmdbar As CommandBar
Dim wkbk As Workbook
' For the Application Object
' Possibly go through each workbook object


For Each wkbk In Application.Workbooks

On Error Resume Next
wkbk.CommandBars("Admin_Supps_and_Objections").Delete
wkbk.CommandBars("Valuers_Supps_and_Objections").Delete
Next wkbk

On Error Resume Next
For Each cntrl In Application.CommandBars("Admin_Supps_and_Objections").Controls
cntrl.Delete
Next cntrl


Application.CommandBars("Admin_Supps_and_Objections").Delete
On Error Resume Next

For Each cntrl In Application.CommandBars("Valuers_Supps_and_objections").Controls
cntrl.Delete
Next cntrl

Application.CommandBars("Valuers_Supps_and_Objections").Delete

Err.Clear
'---------------------------------------------------------------
On Error Resume Next


For Each cntrl In Application.CommandBars("Admin_Supps_and_Objections").Controls
cntrl.Delete
Next cntrl


Application.CommandBars("Admin_Supps_and_objections").Delete
On Error Resume Next

For Each cntrl In Application.CommandBars("Valuers_Supps_and_Objections").Controls
cntrl.Delete
Next cntrl

Application.CommandBars("Valuers_Supps_and_Objections").Delete

Err.Clear

end sub' Delete Old Toolbars

 

[combo]

Have you tested if the toolbar comes with one of excel files (as suggested in my post above)? If the toolbar appears immediately after restarting excel with deleted toolbar, it could be copied or created from any of:
- add-in,
- hidden workbook.
If not, test with what workbook it appears and delete it manually. The code (workbook.commandbars) does not return toolbars attached to the workbook, no way to access it by code.

combo