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