Leftover Excel 2003 Custom Toolbar in 2007 will not delete

QueeniePie

New Member
Joined
Nov 28, 2013
Messages
6
Afternoon,

Many years ago my esteemed former colleague created a very complicated spreadsheet in Excel 2003 which I shall call "Parasite". He departed for sunnier climes a couple of years ago and I have since been given the task of updating this spreadsheet. The original spreadsheet contained two custom toolbars, which I have since replaced elsewhere as this spreadsheet is supposed to run in full screen.

These custom toolbars are refusing to shift from their comfy spot. Not only that, they apparently love to colonise every single excel spreadsheet I have (even the new, blank ones) :eek:

I have attempted the following solutions but to no avail:

  1. I right clicked on the toolbars and clicked "Delete Toolbars". This worked until I opened Parasite, the offending spreadsheet again.
  2. I used this code:
    Code:
    [COLOR=#0000ff]Dim[/COLOR] bar [COLOR=#0000ff]As[/COLOR] CommandBar
            [COLOR=#0000ff]For Each [/COLOR]bar [COLOR=#0000ff]In[/COLOR] Application.CommandBars
               [COLOR=#0000ff]If Not [/COLOR]bar.BuiltIn [COLOR=#0000ff]Then
    [/COLOR]          bar.Delete
                [COLOR=#0000ff]End If
    [/COLOR]      [COLOR=#0000ff]Next[/COLOR]
    Again, it worked until I opened Parasite again.
  3. I deleted all the commandbars from a new blank workbook and saved it as a template file in the correct place. Given my current pattern I'm sure you can guess what happened.
  4. I went through every single piece of code in the Parasite workbook, deleting all references to command bars. No change.
  5. I commented out every single bit of code that runs on workbook start up. Nada.

If anyone has any other suggestions I am desperate to try them. The only thing I haven't been able to try is use Excel 2003 to open the file and delete the toolbars in that, save it again and convert it back to 2007 because I don't have Excel 2003 and won't be given access to it.

Thanks for reading through that wall of text! :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,569
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Hi, and welcome to the forum. :)

Was there code in the Workbook_Open event to create commandbars? If not, I suspect you do have attached commandbars. You might try opening the Parasite workbook, then running this:
Code:
Dim bar As CommandBar
        For Each bar In Activeworkbook.CommandBars
          bar.Delete
      Next bar
and see if that works. If not, if you are able to email it, I can check it in 2003 and remove the commandbars if they are present.
 

QueeniePie

New Member
Joined
Nov 28, 2013
Messages
6
Hi Rory,

Thanks for getting back to me!

I've had a go at running that piece of code but it's informing me "Object Required". Changing ActiveWorkbook to Application provides me with a new error: Method 'Delete' of object 'CommandBar' failed.

I'm afraid that due to the nature of the spreadsheet I would not be permitted to email it, but I appreciate your offer.


Queenie
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,569
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
In that case, a little manual work is required - have a look at Doug's last reply in this question.
 

QueeniePie

New Member
Joined
Nov 28, 2013
Messages
6
Rory, you're wonderful.

How I didn't manage to find that when googling I don't know, but you've saved the rest of my hair from being torn out.

To summarise: I saved Parasite as an .xlsm file then changed the extension to a .zip file. Opening the xl folder within the zip file I was able to find attachedtoolbars.bin and delete the heck out of it. I changed Parasite's extension back to an .xlsm file, opened it up, deleted the toolbars one last time, saved as .xls (2003 compatible) and then reopened it to find no more toolbars :)

Many thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,569
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,569
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :)
 

Forum statistics

Threads
1,085,724
Messages
5,385,524
Members
401,957
Latest member
Socksnpants

Some videos you may like

This Week's Hot Topics

Top