.Display only on the this workbook

[Nole321]

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?

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

 

[Tom Urtis]

To do that, put this into your workbook module, which in most cases is named ThisWorkbook:

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:

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

Test12Reverse

macro.

(2)
I suggest you preface the first macro with the

Private

statement such as I did for the

Private Sub Test12Reverse

macro.

 

[Nole321]

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.

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