hiding all command bars

DerekF

Board Regular
Joined
May 28, 2006
Messages
138
Hello All,

I have a macro here that hide all of my command bars on workbook open. ( a similar macro restores them on workbook close). However, this only works flawlessly on my computer. If someone else opens the workbook with different commandbar settings, it of course does not hide all their's, which is the desired result so that they can not mess around with the workbook and are forced to use the custom buttons on the workbook.

Is there a command to temporarily hide all commandbars and menus from any user that opens this workbook, regardless of their individual settings?

here is the marco the runs on workbook open:


Code:
Sub RemoveToolbars()
    On Error Resume Next
    Worksheets("QC").Activate
        With Application
           .CommandBars("Full Screen").Visible = False
           .CommandBars("Formatting").Visible = False
           .CommandBars("Standard").Visible = False
           .CommandBars("Worksheet Menu Bar").Enabled = False
           .CommandBars("Worksheet Menu Bar").Visible = False
           .CommandBars("Formulabar").Visible = False
           .CommandBars("Formulabar").Enabled = False
           .CommandBars("ControlToolbox") = False
           .DisplayScrollBars = False
           .DisplayStatusBar = True
           .DisplayFullScreen = False
           .Height = "470"
           .Width = "573"
           .EnableResize = False
                      
        End With
        
   End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DerekF

Board Regular
Joined
May 28, 2006
Messages
138
Thanks. I looked at that post a few days ago and it gave me some ideas to consider.

It is not really the road I need to go for the short term as I need a quick fix, but I do think when I have some time I will impliment some of them.

If I had a list of all the command bar Excel has that would help, then I could just set them to visible=false. I can't seem to find a list of all the available ones anywhere.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You can use:

Code:
Sub HideAllCBs()
    Dim x As CommandBar
        For Each x In Application.CommandBars
            x.Enabled = False
        Next x
End Sub

Just make sure to set it back to True at the end.

But you're prone to upset some users if they have some custom views setup. In that case you'll need to test for their current settings, save them, then restore them at the end. I believe there's a good example on the MSKB.

HTH,

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top