Hide all visible commandbars on open

[JohnAcc]

Hi all,

How can I disable all commandbars when openening a spreadsheet. At the moment I have:

Private Sub Workbook_Open()
For Each cmdbar In CommandBars
cmdbar.Visible = False
Next
fmMain.Show
End Sub

I would also like to reset the commandbars when closing the file.

Any help is much appreciated!

 

[VBAjedi]

As far as resetting the commandbars when you close the file: it's up to you to keep track of what was open. I've seen some people store each commandbar's name in a hidden sheet, and I've seen others write them to a text file. Then you put code in the workbooks BeforeClose event that restores all the commandbars.

VBAjedi

 

[RBVBA]

One way that I have used is to put all of the toolbar names into an array on opening the office application, making them invisible as you do. Then, use the close event to restore visibility on exit.
Be careful of that code you have there. Do not use it in your close event or you will get an awful lot of toolbars all of a sudden. You might need to use ENABLED=False to remove a menu bar-you cannot usually make them invisible.
If you do, you must use ENABLED=True in the close event to restore the menu bar, since it must be put back programatically. Also look at the bar type, (menu bars,floating bars and toolbars all have different type numbers if I recall, to distinguish them). Regards, RBVBA.

 

[KenWright]

And if you can live with just the file menu then you can just use the full screen option:-

Sub FS()
Application.DisplayFullScreen = True
End Sub

and then reset with

Sub Norm()
Application.DisplayFullScreen = False
End Sub

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

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

 

[byundt]

In my Excel 2003, there are some 135 command bars. Of those, over 30 will cause a fatal error if you try to change their visible property to False.

I suggest that you list your command bars using cmdbar.name on a worksheet as you loop through them. You can then pick out the ones of interest, and make those not visible programmatically, but saving their Visible property in a Public Boolean array

You would then need a Workbook_Deactivate sub (or perhaps a Workbook_BeforeClose sub) to restore them.