close (hide) toolbars

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
Can anybody advise how I can close or hide the 'Formatting', 'Standard',
'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel
worksheet is opened, but NOT affect their apperance in any other excel
worksheet.
I would imagine it takes some VBA script, but any ideas for a novice ??
thanks
 
sorry Ron but I'm a bit of a novice when it comes to VB.
I have opened the VB code and pasted urs (from ur orig reply) into the
thisworkbook module, but it doesn't seem to do much !
if I open my excel workshhet again , all the toolbars I want to hide are
still there !
HELP PLEASE !!
 
Show me the code you copy in the two events

You can something like this

Private Sub Workbook_Activate()
' This will disable all Command bars except
' the Worksheet Menu Bar
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.Name <> "Worksheet Menu Bar" Then
Cbar.Enabled = False
End If
Next
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub

Private Sub Workbook_Deactivate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.Name <> "Worksheet Menu Bar" Then
Cbar.Enabled = True
End If
Next
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
End Sub


Fore more information about events see
http://www.cpearson.com/excel/events.htm
 
Ron

Fantastic - it worked , however just one thing#

If I have a personal toolbar names 'log' can/how do I change ur code to show
just this toolbar on opening?
thanks again
 
Try this

Sub Disable_Command_Bars_1()
'This will disable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = False
End If
Next
End Sub
 
Ron,
So do I just use the code given in ur last reply to show only my toolbar ??
thanks again
Anthony
 
Hi Anthony

Use this working example

Change YourCustomBar to your bar name

Private Sub Workbook_Activate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.Name <> "Worksheet Menu Bar" And Cbar.Name <> "YourCustomBar" Then
Cbar.Enabled = False
End If
Next
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub

Private Sub Workbook_Deactivate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = True
Next
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
End Sub
 
Back
Top