Hide Excel Toolbars, Ribbon, etc. in a file i'm opening without messing up any other open Workbooks

[cogumelo]

Hi guys,

I'm using the following code to hide all bars and open a workbook to give an app feeling look. I'll call it my EXCEL APP STYLE FILE from now on.

Code i'm using to hide all the stuff and define the width and height is the following:

Sub UIHide()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .WindowState = xlNormal
        .ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",False)"
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .DisplayStatusBar = False
        .DisplayScrollBars = False
        .DisplayFormulaBar = False
        .Width = 800
        .Height = 450
    End With
    With ActiveWindow
        .DisplayWorkbookTabs = False
        .DisplayHeadings = False
        .DisplayRuler = False
        .DisplayFormulas = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

and the code to reset everything back:

Sub UIShow()
    With Application
        .ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",True)"
        .DisplayStatusBar = True
        .DisplayScrollBars = True
        .DisplayFormulaBar = True
    End With
    With ActiveWindow
        .DisplayWorkbookTabs = True
        .DisplayRuler = True
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
End Sub

And then I call these macros

Private Sub Workbook_Open()
    Call UIHide
End Sub
Private Sub Workbook_Activate()
    Call UIHide
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call UIShow
End Sub
Private Sub Workbook_Deactivate()
    Call UIShow
End Sub

When i open the file and close it (and no other excel files are open) things work as i want them to. The file opens with everything hidden and when i close it, it also closes excel and when i open it again with any other excel file everything is back to normal.

However, and here come the bit i'm struggling on, if i open my EXCEL APP STYLE FILE when i already have another file already open, when i close the EXCEL APP STYLE FILE the file that was already open is missing the scrollbars, the formula bar, the status bar and the bottom sheets tab.

1) So an example of what happens with an excel file already open.

2) The EXCEL APP STYLE FILE as it's intended but we can see on the back that the formula bar, the sheets tab and the status bar disappeared in the other file.

3) when i close it, formula bar, scrollbars, status bar and sheets tab are missing.

I'm banging my head trying to tweak the code but i can't seem to fix this so that when my EXCEL APP STYLE FILE doesn't "mess up" any other files except making the changes in itself. Can this be achieved? Any help will be kindly appreciated.

Thx in advance,
cogumelo

 

[johnnyL]

I played with this a bit, I can't seem to have it return all values back to normal either.