PDA

View Full Version : [SOLVED] Hidding Command Bars



Paleo
02-01-2005, 07:59 AM
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
02-01-2005, 08:06 AM
CLOSE!! Try this...


For Each Cmd In CommandBars
Cmd.Enabled = False
Next

Regards,
John

johnske
02-01-2005, 08:19 AM
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
02-01-2005, 10:18 AM
Hi John,

gee, worked great thanks.

Ken Puls
02-01-2005, 10:25 AM
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
02-01-2005, 10:37 AM
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
02-01-2005, 10:42 AM
Hi Carlos,

Try this:


Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False

Paleo
02-01-2005, 11:27 AM
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
02-01-2005, 11:45 AM
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
02-01-2005, 11:45 AM
Gee, this solution is a lot better. Thanks Zack!

Paleo
02-01-2005, 11:49 AM
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