Leftover Excel 2003 Custom Toolbar in 2007 will not delete

[QueeniePie]

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)

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:
   

   [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]

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:

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]

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]

In that case, a little manual work is required - have a look at Doug's last reply in this question.

 

[QueeniePie]

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]

Glad to help.

 

[RoryA]

Glad to help.