troubleshooting Question

compile error variable not defined. ...

Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Excel
9 Comments2 Solutions722 ViewsLast Modified:
Hi experts

I have following macro taken from the link provide by on of the experts on the site...

I want the following macro to hide on opening the spreadsheet and When the file is closed it turns the aforementioned back on

ribbon
formula bar
status bar
tabs

and hide all sheets leaving just one visible....

I cannot get the code to work on opening the workbook.....I keep on getting a compile error....when running the macro.

see link:

Private Sub Workbook_Activate()

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

End Sub

Private Sub Workbook_Deactivate()

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
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros