Removing and replacing toolbars...

[Big Chris]

I found the code below in a search and it's almost exactly what I want
And yes, it works great. However, I want to maintain the Worksheet Men
Bar.....the one with File, Edit, View etc, but not the actua
toolbars.

Would anyone have any idea how I could adjust this code to enable me t
achieve this objective?

Thanks to Bill Manville for his original answer which almost does th
trick for me too, and thanks in advance for looking at my problem.

---------------------------

Sub Auto_Open()
ShowToolbars False
End Sub

Sub Auto_Close()
ShowToolbars True
End Sub

Sub ShowToolbars(ToBeSeen As Boolean)
Dim CB As CommandBar
For Each CB In Application.CommandBars
CB.Enabled = ToBeSeen
Next
End Sub

 

[Rob van Gelder]

Big Chris,

If Not CB.Name = CommandBars.ActiveMenuBar.Name Then CB.Enabled = ToBeSeen

Rob

 

[Big Chris]

Thanks for your help Rob, but I guess I must have put it in the wrong
place as it's now permanently hidden the toolbar I want to keep on show
and I can't get it back.

I used your line like this:

---------------------------

Sub Auto_Open()
ShowToolbars False
End Sub

Sub Auto_Close()
ShowToolbars True
End Sub

Sub ShowToolbars(ToBeSeen As Boolean)
Dim CB As CommandBar
For Each CB In Application.CommandBars
If Not CB.Name = CommandBars.ActiveMenuBar.Name Then CB.Enabled =
ToBeSeen
Next
End Sub

-------------------------------

Could you possibly tell me where I should have put it and maybe how I
can view the VB Editor to change the Autro Open macro? Keystrokes
don't work as the menu bar they relate to is not there!

So at the moment I can't do anything, save maybe reinstalling
Office(?)

Many thanks,

 

[Rob van Gelder]

You put the code on the correct line.

Sounds like your toolbar is "funny". You need to search for an *.xlb file
and rename it to .old (or something) - then restart Excel. See if that
helps.

Rob

 

[Big Chris]

Thanks for coming back to me on this Rob. I found two .XLB files an
renamed them both but it still does what it was doing before.

....any other thoughts you may have would be very gratefully received!

Regards

 

[Rob van Gelder]

Big Chris,

Just make sure you're renaming these files while Excel isn't running.

Try running this code from VBA which should reset all the commandbars:
Sub testit()
Dim cbr As CommandBar

For Each cbr In CommandBars
cbr.Reset
Next
End Sub

Other than that, I'm a little stumped myself..

Rob