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

Question

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. Example of when i open the EXCEL APP STYLE FILE with another 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

Answer 1

I couldn't resolve this within a single workbook, but here's a Work-around that seems to prevent other Workbooks being affected.

It uses a "feeder" workbook that users open, then the feeder workbook can open a new instance of Excel and load the App Workbook. The feeder then closes leaving your App open

The only code needed in the Feeder Workbook is for the WorkBook Open Event ... As per following

Private Sub Workbook_Open()
   Dim oXL As Object
   
   Set oXL = CreateObject("Excel.Application")
   oXL.Workbooks.Open ("C:\YourAPP\YourAPP.xlsm")
   oXL.Visible = True
   AppActivate "YourApp.xlsm"  ' Brings it to Front & gives it the Focus
   If Workbooks.Count = 1 Then
      Application.Quit
   Else
      ThisWorkbook.Close False
   End If
End Sub

You probably need an

Application.Quit

In your App too to ensure that instance of Excel closes

Update : Added the following line - not in original code

   `AppActivate "YourApp.xlsm"  ' Brings it to Front & gives it` the Focus