Hidding Command Bars

[Paleo]

How may I hide all the command bars without knowing their names nor index?

Tried with this:


Sub Macro1()
'
For Each cbar In Application.CommandBars
cbar.Visible = False
Next
End Sub

[johnske]

CLOSE!! Try this...


For Each Cmd In CommandBars
Cmd.Enabled = False
Next

Regards,
John

[johnske]

PS: As this affects all workbooks, to be on the safe side you should also have something like this


Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each Cmd In CommandBars
Cmd.Enabled = True
Next
End Sub

[Paleo]

Hi John,

gee, worked great thanks.

[Ken Puls]

Hi Carlos,

If you are giving the users the option of opening up other Excel books, you may also want to consider throwing something in the Workbook_Activate and Workbook_Deactivate events. That way your users can have their commandbars restored when they move to the other workbook, and removed when they come back.


Private Sub Workbook_Activate()
MsgBox "book1 active!"
End Sub

Private Sub Workbook_Deactivate()
MsgBox "book1 deactivated!"
End Sub

Cheers,

[Paleo]

Gee, great idea Ken, thanks.



But, as a matter of fact, now I need something more. Is there a way I may hide the rows and columns labels? And what about hidding the formulas bar, is it possible?

[Ken Puls]

Hi Carlos,

Try this:


Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False

[Paleo]

Hi Ken, perfect, this time worked great.

This is the final code:


Private Sub Workbook_Activate()
For Each cbars In Application.CommandBars
cbars.Enabled = False
Next
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cbars In Application.CommandBars
cbars.Enabled = True
Next
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
End Sub

Private Sub Workbook_Deactivate()
For Each cbars In Application.CommandBars
cbars.Enabled = True
Next
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
End Sub

Private Sub Workbook_Open()
For Each cbars In Application.CommandBars
cbars.Enabled = False
Next
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
End Sub

[Zack Barresse]

Hey Carlos,

If you will have any more of this to do, you might want to think about just writing a normal Sub routine for this, then calling from your events. Something like ...


In a Standard Module:

In your ThisWorkbook Module:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Turn_On
End Sub

Private Sub Workbook_Deactivate()
Call Turn_On
End Sub

Private Sub Workbook_Activate()
Call Turn_Off
End Sub

Private Sub Workbook_Open()
Call Turn_Off
End Sub[/vba]
In a Standard Module:
[vba]Sub Turn_Off()
Dim cbars As CommandBar
For Each cbars In Application.CommandBars
cbars.Enabled = False
Next
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
End Sub

Sub Turn_On()
Dim cbars As CommandBar
For Each cbars In Application.CommandBars
cbars.Enabled = True
Next
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
End Sub

[Paleo]

Gee, this solution is a lot better. Thanks Zack!

[Paleo]

Okay, now this is my final code. With some changes.

In This Workbook Module


Private Sub Workbook_Activate()
Call TurnOff
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call TurnOn
End Sub

Private Sub Workbook_Deactivate()
Call TurnOn
End Sub

Private Sub Workbook_Open()
Call TurnOff
End Sub


In Standard Module


Sub TurnOff()
For Each cbars In Application.CommandBars
cbars.Enabled = False
Next
ActiveWindow.DisplayHeadings = False
With Application
.DisplayFormulaBar = False
.Caption = "My Application"
End With
End Sub
Sub TurnOn()
For Each cbars In Application.CommandBars
cbars.Enabled = True
Next
ActiveWindow.DisplayHeadings = True
With Application
.DisplayFormulaBar = True
.Caption = "Microsoft Excel"
End With
End Sub