.Display only on the this workbook

Nole321

New Member
Joined
Jun 2, 2021
Messages
7
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello all,

I have these parameters below that I am trying to apply to a workbook to make it into a clean form view. My issue is that I am trying to apply this view to only this workbook and no other. As soon as I apply the code to run when open, it will also apply it to every workbook I open thereafter until I close it (but not on any file that was currently open). Is there a way to apply this view to only this workbook so other excel files can be opened as normal excel view?

VBA Code:
Sub Test12()

ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayHeadings = False
Application.DisplayStatusBar = False
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"

'With Application
'    .WindowState = xlNormal
'    .Width = 300
'    .Height = 300
'End With

'Sheet1.ScrollArea = "A1:J30"

End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
To do that, put this into your workbook module, which in most cases is named ThisWorkbook:

VBA Code:
Private Sub Workbook_Open()
Run "Test12"
End Sub

Private Sub Workbook_Activate()
Run "Test12"
End Sub

Private Sub Workbook_Deactivate()
Run "Test12Reverse"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "Test12Reverse"
End Sub


Then, put this macro in the module where your Test12 macro is:

VBA Code:
Private Sub Test12Reverse()

ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"

'With Application
'    .WindowState = xlNormal
'    .Width = 300
'    .Height = 300
'End With

'Sheet1.ScrollArea = "A1:J30"

End Sub

Two things:

(1)
I left alone the commented out lines because they are commented out, but you get the idea about how to adjust those code lines in the
VBA Code:
Test12Reverse
macro.

(2)
I suggest you preface the first macro with the
VBA Code:
Private
statement such as I did for the
VBA Code:
Private Sub Test12Reverse
macro.
 
Solution

Nole321

New Member
Joined
Jun 2, 2021
Messages
7
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you! I did already have all the code in 'ThisWorkbook" to run when open and return everything true when closed. I just had it in that module for testing. But I do understand what you did with "Workbook_Deactivate()" and "Workbook_Activate()" to change those display values for the next workbook!. Thanks for the help!

This is how I have it set up in excel object ThisWorkbook for anyone in the future that reads this.

VBA Code:
Private Sub Workbook_Open()
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayHeadings = False
Application.DisplayStatusBar = False
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End Sub

Private Sub Workbook_Activate()
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayHeadings = False
Application.DisplayStatusBar = False
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End Sub

Private Sub Workbook_Deactivate()
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
End Sub
 
Last edited:

Forum statistics

Threads
1,148,159
Messages
5,745,118
Members
423,925
Latest member
globaltlg

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