PDA

View Full Version : Hide / Unhide toolbars for a particular worksheet



sindhuja
03-07-2009, 11:04 PM
Hi,

Want to hide all the toolbars except the menubar using coding.... and unhide while closing the excel file.

Tried some coding..... its working for the all the excel applications. want tis coding to be applicabel only for the particular excel file...

-Sindhuja

Aussiebear
03-08-2009, 01:31 AM
Would you like to show the code you have so far?

Bob Phillips
03-08-2009, 03:57 AM
Option Explicit

Public WithEvents App As Application

Private mFormulaBar

Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
Dim oCB As CommandBar
If Wb.Name = "myfile.xls" Then

For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End If
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Dim oCB As CommandBar
If Wb.Name = "myfile.xls" Then

For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False

End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

GTO
03-08-2009, 08:21 AM
Want to hide all the toolbars except the menubar...

Greetings Sinduja,

An ever-so-slight tweak to Bob's code:

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Dim oCB As CommandBar

If Wb.Name = "myfile.xls" Then

For Each oCB In Application.CommandBars
If Not oCB.Name = "Worksheet Menu Bar" _
Then oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False

End If
End Sub

Hope that helps,

Mark

sindhuja
03-10-2009, 03:45 AM
Thank you everyone for all your help..
Its works perfectly.......

-sindhuja

sindhuja
03-20-2009, 02:03 PM
Toolbars and menu bar missing - while opening XL 2003

Any help please...

GTO
03-20-2009, 03:08 PM
Greetings sindhuja,

Say, you zipped an empty workbook:wot

Mark

sindhuja
03-24-2009, 04:58 AM
Hi Mark...

When I open the excel application its not showing the menu bars and all the custom menus….

Its for all the excel file I open… don’t know how to proceed further to view all the menus..

Attachment was the sample excel…

Any help please...

-Sindhuja

Bob Phillips
03-24-2009, 05:05 AM
Because, as Mark said, your workbook doesn't contain any code, never mind the code we gave you. You have to install the code.