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

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
179
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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

VBA Code:
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.
1.JPG


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.

2.png

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

open with other file open.JPG


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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,849
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I played with this a bit, I can't seem to have it return all values back to normal either. :(
 

Forum statistics

Threads
1,148,156
Messages
5,745,101
Members
423,923
Latest member
yuvaraj859

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top