Temporarily delete ALL CommandBars?

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I want to temporarily delete ALL command bars in one
instance of Excel without effecting the permanent layout
of the excel command bars. I cannot get the following
code to work but I can get other variations of to work
using the .visible property and I cannot do anything to
the "Worksheet Menu Bar" without causing errors.
Manipulating the .visible property affects the permanent
layout of Excel in new instances created. Can anyone
offer an alternative to what I'm trying to do? Your help
is appreciated.

-Kevin
(sample code below)

'Temporarily delete any existing toolbar
For Each cb In CommandBars
cb.Delete(True)
Next cb

'create the new Toolbar
Dim cbPGD As CommandBar
Set cbPGD = CommandBars.Add("PGD", msoBarTop, , True)
With cbPGD
.Enabled = False
.Visible = False
End With
 
for the worksheet menu bar, you have to use enabled = False

something like:

for each cb in commandbars
if cb.Name = "Worksheet Menu Bar" then
cb.Enabled = False
else
cb.Visible = False
end if
Next
 
I tried it and the .enabled property worked for getting
rid of the "Worksheet Menu Bar" BUT it did it for all
instances of Excel. I only want to supress the built in
command bars for the one particular instance of Excel. Do
you have any other tricks that might help?

Thanks for your help.
-Kevin
 
You would need to use the activate events for workbooks or worksheets to
adjust the environment as the user moved about in Excel. So you would
hide/unhide/ enable/disable the appropriate elements as the user switched
windows/sheets.

Commandbars are handled at the application level.
 
Back
Top