Workbook Event Producing an Error

Hello,

I have a workbook that I want to turn off (or hide) the Ribbon, Formula Bar, Status Bar and the Worksheet Tabs and then go to a specific sheet when opened. I found some code that seems to do all of that but comes up with an error message during opening and closing the file, and when opening, it also ends up not going to the specific worksheet that I want it to. The original code I found is located on this web page: http://www.mrexcel.com/forum/excel-q...-workbook.html

I placed all of it, with the addition of calling a macro to cause the worksheet I want to be viewed, into ThisWorkbook. The reason for the Workbook_Activate and Deactivate is so these events only affect this specific workbook and not others that are opened. The code looks like the following:

Option Explicit

Private Sub Workbook_Open()

Set myRange = ActiveSheet
   Application.ScreenUpdating = False
   Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
   Application.DisplayFormulaBar = False
   Application.DisplayStatusBar = Not Application.DisplayStatusBar
   ActiveWindow.DisplayWorkbookTabs = False

   Dim wbBook As Workbook
   Dim wsSheet As Worksheet

   Set wbBook = ThisWorkbook

   For Each wsSheet In wbBook.Worksheets
      If Not wsSheet.Name = "Blank" Then wsSheet.Activate
         With ActiveWindow
            .DisplayHeadings = False
            .DisplayGridlines = False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
            'ActiveSheet.EnableSelection = xlUnlockedCells
         End With
   Next wsSheet

myRange.Select

    Call GoTo_Start
    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set myRange = ActiveSheet
   Application.ScreenUpdating = False
   Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
   ActiveWindow.DisplayWorkbookTabs = True

   Dim wbBook As Workbook
   Dim wsSheet As Worksheet

   Set wbBook = ThisWorkbook

   For Each wsSheet In wbBook.Worksheets
      If Not wsSheet.Name = "Blank" Then wsSheet.Activate
         With ActiveWindow
           .DisplayHeadings = True
           .DisplayGridlines = True
           ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
          'ActiveSheet.EnableSelection = xlUnlockedCells
         End With
   Next wsSheet

myRange.Select

End Sub

Private Sub Workbook_Activate()

    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
    Application.ScreenUpdating = True

End Sub

Private Sub Workbook_Deactivate()

    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True

 End Sub

When executed upon opening, it hides everything as it is supposed to but doesn't run the macro "GoTo_Start" (bolded above) to land on the worksheet I want it to. It then pops up the following error message: "Compile error in hidden module: ThisWorkbook. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application." When I click the Ok button I end up on whichever sheet was visible when last saved instead of the correct start sheet. I can then do whatever I want to the workbook, adding records, etc., but when I go to close the file, I receive another error message: "Compile error: Variable not defined" and it then highlights the areas in yellow(ish) and blue font above. After stopping the code I can save the file and close it.

Thank you very much for any help or suggestions you can offer!

Very sorry if you've already spent some time on this. I just figured out the problem and it now works perfectly without any errors and starts me on the correct sheet. FYI - Also this makes sure to not affect other workbooks. Revised code below.

Option Explicit
Private Sub Workbook_Open()

Dim myRange As Range

On Error Resume Next

Application.EnableEvents = False

Set myRange = ActiveSheet
   Application.ScreenUpdating = False
   Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
   Application.DisplayFormulaBar = False
   Application.DisplayStatusBar = Not Application.DisplayStatusBar
   ActiveWindow.DisplayWorkbookTabs = False

   Dim wbBook As Workbook
   Dim wsSheet As Worksheet

   Set wbBook = ThisWorkbook

   For Each wsSheet In wbBook.Worksheets
      If Not wsSheet.Name = "Blank" Then wsSheet.Activate
         With ActiveWindow
            .DisplayHeadings = False
            .DisplayGridlines = False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
         End With
   Next wsSheet

myRange.Select

Application.EnableEvents = True

    Call GoTo_Start
    
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim myRange As Range

On Error Resume Next

Application.EnableEvents = False

Set myRange = ActiveSheet
   Application.ScreenUpdating = False
   Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
   ActiveWindow.DisplayWorkbookTabs = True

   Dim wbBook As Workbook
   Dim wsSheet As Worksheet

   Set wbBook = ThisWorkbook

   For Each wsSheet In wbBook.Worksheets
      If Not wsSheet.Name = "Blank" Then wsSheet.Activate
         With ActiveWindow
           .DisplayHeadings = True
           .DisplayGridlines = True
           ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
         End With
   Next wsSheet

myRange.Select

Application.EnableEvents = True
End Sub
Private Sub Workbook_Activate()

    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
    Application.ScreenUpdating = True

End Sub
Private Sub Workbook_Deactivate()

    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True

 End Sub