Code locks open files, not just one being opened

[Sharon in Denver]

Hello you wise VBA sages! I have a little piece of VBA that runs on select files to lock it down in order to keep users from entering data or altering formatting. It works great! (Thank you!)

I've noticed, though, that is also locks any other files you may already have open. It has a password, so this obviously presents a problem, as the other file remains locked and it shouldn't be. How can I alter the code to only run on the file in which it is embedded? Or is there another solution I'm overlooking?

Here is the code:

Private Sub Workbook_Open()
'
' Protection Macro
' Macro written by Sharon Van Dyke
'
Dim Sheetnumber
Sheetnumber = 1
Do
ActiveWorkbook.Worksheets(Sheetnumber).Activate
ActiveWorkbook.Worksheets(Sheetnumber).Protect "surt"
Sheetnumber = Sheetnumber + 1
Loop Until Sheetnumber = ActiveWorkbook.Worksheets.Count
End Sub

Thank you in advance!

 

[jmthompson]

Maybe change ActiveWorkbook to ThisWorkbook or specify the workbook by name Workbooks("MineToEdit.xlsm").Worksheets(Sheetnumber).Protect "surt"

 

[Sharon in Denver]

Jen, initial tests look like that might be the solution! Thank you so much!

 

[shg]

Another way:

Private Sub Workbook_Open()
    Dim wks As Worksheet
    
    For Each wks In Me.Worksheets
        wks.Protect "surt"
    Next wks
End Sub