Excel cells unlocking for no (apparent) reason

[MiguelG]

Hello everyone! I have been working on an Excel spreadsheet for a master thesis, and this is my first time going around and using VBA.

What I am trying to do is have each specific sheet in my workbook be protected against accidental changes of some cells.
Therefore, I am running this code on workbook open:

Private Sub Workbook_Open()


Dim ws As Worksheet
Dim MyMultipleRnage As Range




For Each ws In Worksheets
    If (ws.Name = ShData3Months.Name) Then
        Set MyMultipleRange = Union(Range("E1:J1"), Range("M1:R1"), Range("U1:V1"), _
            Range("A3:XFD1048576"), Range("Y1:XFD2"))
        MyMultipleRange.Locked = False
    End If
    ws.Protect Password:="leugim", DrawingObjects:=False, _
        UserInterFaceOnly:=True, Contents:=True


Next ws


End Sub

As you can see, in one of the sheets (where user inputs data) I let some cells be altered.
I am working with 11 sheets and everything is protected as intended, except for ONE specific sheet (it is the very first sheet of the workbook). After some testing, I have noticed that it is indeed locking cells and protecting the sheet, but instead of locking entire sheet, it locks the ranged I specified for "ShData3Months". I have tried to manually lock entire sheet and then have it protected, but when I re-run the code it just goes back to where it was.

I can't seem to find a reason behind this. Would appreciate some help!

Thanks in advance,
MG

 

[Fluff]

That code is not locking any cells, it's unlocking cells on the activesheet only, regardless of the sheet name.
Can you please explain what you are trying to do

 

[MiguelG]

That code is not locking any cells, it's unlocking cells on the activesheet only, regardless of the sheet name.
Can you please explain what you are trying to do

It is my understanding that cells are locked by default, so I figured I would just unlock the specific range in the specific sheet I want them to be unlocked, and then simply protect all sheets.

What I want is to have all sheets locked and protected, except that specific range.

 

[Fluff]

How about

Private Sub Workbook_Open()
   Dim ws As Worksheet
   Dim MyMultipleRnage As Range
   
   For Each ws In Worksheets
      ws.Cells.Locked = False
      ws.Range("A1:D1,K1:L1,S1:T1,W1:X1,A2:X2").Locked = True
      ws.Protect Password:="leugim", DrawingObjects:=False, _
           UserInterFaceOnly:=True, Contents:=True
   Next ws
End Sub

 

[MiguelG]

Your suggested code appears to not work, for two reasons:
1. The range of cells I want unlocked have some merged cells, and I've been seeing that they cause a LOT of issues when it comes to making them do stuff correctly, such as selecting ranges, etc.
2. I can't change the locked value of already protected sheets. So, even after unmerging the cells, everything would work just one time. Opening the workbook one next time would end up giving me an error at line 5 of the code because of the reason i previously stated.

After going over my though process one more time, I ended up realising I was better off simply manually locking/unlocking each cells I needed just once, and then having this simple code working on workbook open:

I ended up at:

Private Sub Workbook_Open()    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect Password:="leugim", DrawingObjects:=False, _
            UserInterFaceOnly:=True, Contents:=True
    Next ws
End Sub

Since I'm not doing any silly stuff trying to alter the locked value on some specified sheets, they now seem to all maintain my manual inputs even after several runs of the code! I'll be happy with this for now

Thank you for your time!

 

[Fluff]

Glad you've sorted it & thanks for the feedback