Can't Change Commandbars("Worksheet Menu Bar")

[Tim]

Hi NG;

Recently, my XL2000 installation stopped allowing programmatic changes to
the Worksheet Menu Bar. Code like this causes a
"Run-time error '-2147467259 (80004005)':
Method 'Add' of object 'CommandbarControls' failed"

Sub AddMenuItem()
Dim cb as CommandBar, cbp as CommandBarPopup
Set cb = Application.Commandbars("Worksheet Menu Bar")
Set cbp = cb.Controls.Add(Type:=msoControlPopup, Before:=8,
Temporary:=True) 'this is the line that causes the failure
'...
Set cb = Nothing: Set cbp = Nothing
End Sub

Can anyone tell me how to fix my installation? I have already completely
removed my Office installation, reinstalled and obtained and installed all
SPs. The problem is limited to Excel, Word and the other apps all allow
this modification. Also, I cannot manually add a new menu item with
[right-click] Customize...

TIA

 

[Ron de Bruin]

Hi Tim

Try this

Often this is a corrupt or bloated toolbar file
*normal* size is < 30 kb.
The .xlb file has all Toolbar customization in it.

Close Excel
Do a search for .xlb in windows
Rename the file you find (my file is named Excel10.xlb in 2002)
Start Excel

Deleting the file or renaming will do no harm on your system
Excel will create a new one for you.
(You lost your customization remember that)

If you make your own toolbars or add buttons to the others
This file is important (backup it so you can restore it)

 

[Tim]

Thanks, Ron. This did the trick!

Hi Tim

Try this

Often this is a corrupt or bloated toolbar file
*normal* size is < 30 kb.
The .xlb file has all Toolbar customization in it.

Close Excel
Do a search for .xlb in windows
Rename the file you find (my file is named Excel10.xlb in 2002)
Start Excel

Deleting the file or renaming will do no harm on your system
Excel will create a new one for you.
(You lost your customization remember that)

If you make your own toolbars or add buttons to the others
This file is important (backup it so you can restore it)


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Hi NG;

Recently, my XL2000 installation stopped allowing programmatic changes to
the Worksheet Menu Bar. Code like this causes a
"Run-time error '-2147467259 (80004005)':
Method 'Add' of object 'CommandbarControls' failed"

Sub AddMenuItem()
Dim cb as CommandBar, cbp as CommandBarPopup
Set cb = Application.Commandbars("Worksheet Menu Bar")
Set cbp = cb.Controls.Add(Type:=msoControlPopup, Before:=8,
Temporary:=True) 'this is the line that causes the failure
'...
Set cb = Nothing: Set cbp = Nothing
End Sub

Can anyone tell me how to fix my installation? I have already completely
removed my Office installation, reinstalled and obtained and installed all
SPs. The problem is limited to Excel, Word and the other apps all allow
this modification. Also, I cannot manually add a new menu item with
[right-click] Customize...

TIA