Save workbook, close, open it, macro effects are gone

[frewert]

Ill post all of the code so you understand.

Sub blank_copy()
    
    Dim range1 As Range
    Set range1 = ThisWorkbook.Sheets("Sheet1").Range("A87:K127")
    
    range1.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Rows("1:57").Select
    Selection.RowHeight = 13
    ActiveSheet.Buttons.Add 749.25, 102.75, 132.75, 25.5
    ActiveSheet.Paste
    ActiveWindow.Zoom = 103
    
    Call Printer
    Call InsertPicture
    Call locker
    Call freezer
   
End Sub

Sub Printer()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        
        
        With ActiveSheet.PageSetup
            .RightFooter = "&P of &N"
            .CenterFooter = "&F"
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .Zoom = False
            .Orientation = xlLandscape
            .PrintArea = "$A$1:$G$40"
        End With
        
    Next ws
  
    
End Sub

Sub locker()
    ActiveSheet.Protect "epc", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Sub freezer()
    
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
         
        With ActiveSheet
            .ScrollArea = "A1:G40"
        End With
        
    Next ws
    
End Sub

Private Sub Workbook_open()
    Sheet2.Activate
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Filename = "W:\EPC Stationary and Logos\LOGOS\EPC Logos\Epc_logo_nobg.png"
        .Height = 120
        .Width = 180
        .Brightness = 0.36
        .ColorType = msoPictureGrayscale
        .Contrast = 0.39
        .CropBottom = 0
        .CropLeft = 0
        .CropRight = 0
        .CropTop = 0
    End With
    ActiveSheet.PageSetup.RightHeader = "&G"
    ActiveSheet.PageSetup.RightFooter = "&P of &N"
    ActiveSheet.PageSetup.CenterFooter = "&F"
    ActiveSheet.Protect "epc", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.ScrollArea = "A1:G40"
       
End Sub

Explanation:
This workbook is a template for data input.
You open it, and you have a blank template, and command buttons to the right of the template.
The command buttons, you click, and creates a new sheet, formats the sheet, and pastes the template you want.
I also have only certain cells unlocked. The sheets all are protected. All the sheets have disabled scrolling too.

Problems:
When you open the workbook the initial sheet is supposed to have disabled scrolling. However, the code for this is not working.

You can press the command buttons for new templates sheets, and when created they are protected and scrolling is frozen as should be. But when you save and reopen the workbook, these sheets are no longer scroll locked.

Solution:
Looks like I probably just need to do some kind of loop through all sheets upon opening the workbook for all of my effects to be applied.

 

[Andrew Poulsom]

Where have you put your Workbook_Open procedure? I notice that the word open isn't in proper case.

And this isn't right:

Sub freezer()
    
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
         
        With ActiveSheet
            .ScrollArea = "A1:G40"
        End With
        
    Next ws
    
End Sub

You haven't Activated the worksheet in your loop, so you need to use ws rather than ActiveSheet.

 

[frewert]

I put code from "freezer" in with my code on ThisWorkbook, the Workbook_Open procedure. Everything is working as should be now.