Prevent Code from Affecting a New Instance of Excel

[Marhier]

Hi everyone, I hope you're well.
I've created a workbook that when opened, you're greeted with a dashboard of spliced pivot charts.
I've made it so when opened, it removes the following:

Scroll bars.
Formula Bar.
Headings.
Toolbar.
Ability to scroll.

The issue I've got is, I don't want it to affect a new instance of Excel, or if someone opens another workbook.

My code is as follows:

Private Sub Workbook_Open()
If ThisWorkbook.Name = "Purchasing Spend Source Data.xlsm" And ActiveWorkbook Is ThisWorkbook Then
    Dim ws As Worksheet
    Dim Sheet As Worksheet, Pivot As PivotTable
    Dashboard.Select
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = "Dashboard" Then
            ws.Unprotect
            DisplayHorizontalScrollBar = False
            DisplayVerticalScrollBar = False
            Application.DisplayFormulaBar = False
            ActiveWindow.DisplayHeadings = False
            Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
            Sheets("Dashboard").ScrollArea = "A1"
            ws.Protect
        End If
    Next ws
    For Each Sheet In ThisWorkbook.Worksheets
    For Each Pivot In Sheet.PivotTables
        Pivot.RefreshTable
        Pivot.Update
    Next
    Next
End If
End Sub

I've tried to tackle this with the If ThisWorkbook.Name = - along with if it's the active workbook
Is what I'm trying to do even possible?

Any help would be greatly appreciated.

Thank you.
Regards
Marhier.

 

[kgkev]

I use a similar scenario for recreating the right click menu. I am sure this can be adapted to reset everything you have changed.
just copy most of your above code and replace the False with true.

you need three bits of code.
On Close
On change focus - OFF
on change Focus - On

Private Sub Workbook_Close()
'CODE TO RESET EVERYTHING YOU HAVE DONE
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'CODE TO RESET EVERYTHING YOU HAVE DONE
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'CODE TO RESET EVERYTHING YOU HAVE DONE
End Sub

 

[Marhier]

Many thanks for the fast response.
I gave this a go, but if the code that hides everything is active, when I open a new instance of Excel, or another saved workbook, the effect still remains.
Only difference is now is if I close the new or other workbook, it brings everything back on the dashboard, lol.

 

[kgkev]

Have you got the code in the "This Work Book" section of "Purchasing Spend Source Data.xlsm"?

 

[Marhier]

I do, yeah.

 

[kgkev]

OK, I think this should work
I guess you can leave your On open event as it is.

If you place this code in the "Dashboard" object

Private Sub Worksheet_activate()
        ActiveSheet.Unprotect
        ActiveWindow.DisplayHorizontalScrollBar = False
        ActiveWindow.DisplayVerticalScrollBar = False
        ActiveWindow.Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
        Sheets("Dashboard").ScrollArea = "A1"
        ActiveSheet.Protect
End Sub

Private Sub Worksheet_deactivate()
    ActiveSheet.Unprotect
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
    ActiveWindow.Application.DisplayFormulaBar = True
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
    ActiveSheet.Protect
End Sub

and this in the workbook objected

Private Sub Workbook_activate()
        ActiveSheet.Unprotect
        ActiveWindow.DisplayHorizontalScrollBar = False
        ActiveWindow.DisplayVerticalScrollBar = False
        ActiveWindow.Application.DisplayFormulaBar = False
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
        Sheets("Dashboard").ScrollArea = "A1"
        ActiveSheet.Protect
End Sub

Private Sub Workbook_deactivate()
    ActiveSheet.Unprotect
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
    ActiveWindow.Application.DisplayFormulaBar = True
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
    ActiveSheet.Protect
End Sub

 

[kgkev]

adding
Application.ScreenUpdating = False
to the top of each line makes the screen update slightly less jerky, but you can still see the elements disappear.

 

[Marhier]

Oh, you absolute HERO!!!!
That worked a treat!

Legendary status confirmed.

Thank you!

 

[kgkev]

No problem.
feel like I have paid my dues. Came here looking for help and was glad I could help someone else while my question was being answered.