Removing and replacing toolbars...

  • Thread starter Thread starter Big Chris
  • Start date Start date
B

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
 
Big Chris,

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

Rob
 
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,
 
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
 
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
 
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
 
Back
Top