Show/Hide toolbars for current workbook only

GuyGadois · May 9th 2006

I have a macro that starts when the workbook is open that hides many of the headers and toolbars. It is great and works fine unless you are working on another workbook and need to see those headers and tool bars. Is there a way to display and hide those items per workbook and not for the entire application. here is my failed attempt:

Code

Sub show_excel_headers()
 
Dim wb As Workbook
Set wb = ThisWorkbook


wb.Application.ScreenUpdating = False


'Display Headings in all worksheets
    Dim wsSheet As Worksheet
    Dim sSheetStart
     
    Set sSheetStart = ActiveSheet


    wb.Application.EnableEvents = False
    For Each wsSheet In Worksheets
        wsSheet.Activate
        ActiveWindow.DisplayHeadings = True
    Next
    sSheetStart.Activate
    wb.Application.ScreenUpdating = False
    wb.Application.EnableEvents = True


'Showing windows
    ActiveWindow.DisplayHeadings = True
    With wb.Application
        .ShowStartupDialog = True
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ShowWindowsInTaskbar = True
    End With
    
wb.Application.CommandBars("Standard").Visible = True
wb.Application.CommandBars("Formatting").Visible = True
wb.Application.CommandBars("Worksheet Menu Bar").Visible = True


wb.Application.ScreenUpdating = True


End Sub

Display More

Code

Sub hide_excel_headers()


Dim wb As Workbook
Set wb = ThisWorkbook


wb.Application.ScreenUpdating = False


'Removing Display Headings
    Dim wsSheet As Worksheet
    Dim sSheetStart


    Set sSheetStart = ActiveSheet
    wb.Application.ScreenUpdating = False
    wb.Application.EnableEvents = False
    For Each wsSheet In wb.Worksheets
        wsSheet.Activate
        ActiveWindow.DisplayHeadings = False
    Next
    sSheetStart.Activate


    wb.Application.EnableEvents = True


'Hiding toolbar stuff
    ActiveWindow.DisplayHeadings = False
    With wb.Application
       .ShowStartupDialog = False
       .DisplayFormulaBar = False
       .DisplayStatusBar = False
       .ShowWindowsInTaskbar = False
    End With


'Hiding menu
    wb.Application.CommandBars("Standard").Visible = False
    wb.Application.CommandBars("Formatting").Visible = False
    wb.Application.CommandBars("Drawing").Visible = False
    wb.Application.CommandBars("Worksheet Menu Bar").Visible = False 'THIS IS ALSO SHOWING A BUG NOW - WHY?


wb.Application.ScreenUpdating = True


End Sub

Display More

Any help is appreciated.

Guy

HaHoBe · May 10th 2006

Re: Show/Hide toolbars for current workbook only

Hi, GuyGadois,

you may have a good look at ThisWorkbook and the Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window) and Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window) events to call your codes when changing workbooks.

Ciao,
Holger

**royUK** · May 10th 2006

Re: Show/Hide toolbars for current workbook only

Guy

I wouldn't recommend this approach, it can cause real aggro for your users if for instance Excel crashes & doen't restore everything back to the original settings

Dave Hawley · May 10th 2006

Re: Show/Hide toolbars for current workbook only

I would use the Workbook Active and Deactivate to do this. E.g

Code

Private Sub Workbook_Activate()
    On Error Resume Next
    Run "hide_excel_headers"
    On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
    On Error Resume Next
    Run "show_excel_headers"
    On Error GoTo 0
End Sub

Display More

GuyGadois · May 10th 2006

Re: Show/Hide toolbars for current workbook only

Dave, I implemented your code below. First, I get a Run-time error:

Run Time error '-2147467259 (80004005)':
Method 'Visible' of object 'CommandBar' failed

at the following

Code

wb.Application.CommandBars("Worksheet Menu Bar").Visible = False

Also, your code still doesn't seem to do the trick. When I am on, for instance, workbook 2 and click to show headers it shows headers for all workbooks currently open. I only want the code to make the view changes on the active workbook. Even if I have 5 workbooks open I only want the view changes to take place on the active workbook. Here is what I have so far...

Code

Sub show_excel_headers()
 
Dim wb As Workbook
Set wb = ThisWorkbook


wb.Application.ScreenUpdating = False


'Display Headings in all worksheets
    Dim wsSheet As Worksheet
    Dim sSheetStart
     
    Set sSheetStart = ActiveSheet


    wb.Application.EnableEvents = False
    For Each wsSheet In Worksheets
        wsSheet.Activate
        ActiveWindow.DisplayHeadings = True
    Next
    sSheetStart.Activate
    wb.Application.ScreenUpdating = False
    wb.Application.EnableEvents = True


'Showing windows
    ActiveWindow.DisplayHeadings = True
    With wb.Application
        .ShowStartupDialog = True
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ShowWindowsInTaskbar = True
    End With
    
wb.Application.CommandBars("Standard").Visible = True
wb.Application.CommandBars("Formatting").Visible = True
wb.Application.CommandBars("Worksheet Menu Bar").Visible = True


wb.Application.ScreenUpdating = True


End Sub

Display More

Code

Sub hide_excel_headers()


Dim wb As Workbook
Set wb = ThisWorkbook


wb.Application.ScreenUpdating = False


'Removing Display Headings
    Dim wsSheet As Worksheet
    Dim sSheetStart


    Set sSheetStart = ActiveSheet
    wb.Application.ScreenUpdating = False
    wb.Application.EnableEvents = False
    For Each wsSheet In wb.Worksheets
        wsSheet.Activate
        ActiveWindow.DisplayHeadings = False
    Next
    sSheetStart.Activate


    wb.Application.EnableEvents = True


'Hiding toolbar stuff
    ActiveWindow.DisplayHeadings = False
    With wb.Application
       .ShowStartupDialog = False
       .DisplayFormulaBar = False
       .DisplayStatusBar = False
       .ShowWindowsInTaskbar = False
    End With


'Hiding menu
    wb.Application.CommandBars("Standard").Visible = False
    wb.Application.CommandBars("Formatting").Visible = False
    wb.Application.CommandBars("Drawing").Visible = False
    wb.Application.CommandBars("Worksheet Menu Bar").Visible = False


wb.Application.ScreenUpdating = True


End Sub

Display More

Code

Private Sub Workbook_Activate()
    On Error Resume Next
    Run "hide_excel_headers"
    On Error GoTo 0
End Sub

Code

Private Sub Workbook_Deactivate()
    On Error Resume Next
    Run "show_excel_headers"
    On Error GoTo 0
End Sub

Quote from Dave Hawley

I would use the Workbook Active and Deactivate to do this. E.g

Code

Private Sub Workbook_Activate()
    On Error Resume Next
    Run "hide_excel_headers"
    On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
    On Error Resume Next
    Run "show_excel_headers"
    On Error GoTo 0
End Sub

Display More

Dave Hawley · May 11th 2006

Re: Show/Hide toolbars for current workbook only

Guy, all my code does is run your code when the Workbook Activates/Deactivates.

DeepPurple · Jun 21st 2006

Re: Show/Hide toolbars for current workbook only

Thanks Dave - you have saved my bacon again! Your suggested code works fine for the app I'm working on at the moment too - but with one minor alteration.
'Run' doesn't seem to call my required 'subs' but using 'call' does.
Any ideas why please?

Quote from Dave Hawley

I would use the Workbook Active and Deactivate to do this. E.g

Code

Private Sub Workbook_Activate()
    On Error Resume Next
    Run "hide_excel_headers"
    On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
    On Error Resume Next
    Run "show_excel_headers"
    On Error GoTo 0
End Sub

Display More

Dave Hawley · Jun 21st 2006

Re: Show/Hide toolbars for current workbook only

Quote

'Run' doesn't seem to call my required 'subs' but using 'call' does.

Are the macros in a standard module? What happens when you use Run?

DeepPurple · Jun 21st 2006

Re: Show/Hide toolbars for current workbook only

They are all 'Private Sub's in the 'ThisWorkbook' object(?) of the spreadsheet.
When using 'Run' the execution just steps right through that line without apparently doing anything.
I have appended the code I am using in case it helps...

Code

Private Sub Workbook_Activate()
    On Error Resume Next
    Call Display_App_Toolbars
    On Error GoTo 0
End Sub
 
Private Sub Workbook_Deactivate()
    On Error Resume Next
    Call Display_User_Toolbars
    On Error GoTo 0
End Sub
Private Sub Define_Toolbar_Collections()
Dim AppBars As Object
Dim tb As Object
    Set AppBars = Application.CommandBars
    For Each tb In AppBars
        If tb.Visible Then
            UserToolbars.Add tb
        End If
    Next tb
    With AppToolbars
        .Add AppBars("Standard")
    '    .Add AppBars("Auditing")
    '    .Add AppBars("Formatting")
    '    .Add AppBars("Control Toolbox")
        .Add AppBars("Unit_calc")
    End With
End Sub


Private Sub Display_App_Toolbars()
Dim AppBars As Object
Dim tb As Object
    On Error Resume Next
    For Each tb In UserToolbars
        tb.Visible = False
    Next tb
    For Each tb In AppToolbars
        tb.Visible = True
    Next tb
End Sub


Private Sub Display_User_Toolbars()
Dim AppBars As Object
Dim tb As Object
    On Error Resume Next
    For Each tb In AppToolbars
        tb.Visible = False
    Next tb
    For Each tb In UserToolbars
        tb.Visible = True
    Next tb
End Sub

Display More

Dave Hawley · Jun 21st 2006

Re: Show/Hide toolbars for current workbook only

Only the Event procedures of the Private module should be in there. All other procedures should be in a standard public module.

Howewer, to call a Private Sub in the same Private Module, you simply put the Procedures name. E.g

Code

Private Sub Workbook_Deactivate() 
    On Error Resume Next 
    Display_User_Toolbars 
    On Error Goto 0 
End Sub

mankit87 · Oct 12th 2011

Re: Show/Hide toolbars for current workbook only

Hi,

change visible to enable.

wb.Application.CommandBars("Worksheet Menu Bar").Enabled = False

Show/Hide toolbars for current workbook only

Participate now!

Share