Strange phenomenon when locking cells with VBA I have a workbook setup as calend

Question

Strange phenomenon when locking cells with VBA

I have a workbook setup as calender with sheets for every week.

To start a new one for the next year I wrote several subs which seemed to work fine until I met a strange behaviour with the procedure that was locking cells.

There is a lot more code which should explain the unnecessary with-structures, but allow me to concentrate on this small part which behaves strangely:

...

For i = 1 To 53

Worksheets("KW " & i).Unprotect Password:="secretstuff"

With Worksheets("KW " & i).Range("I1:M200")

    .Locked = True

End with

With worksheets("KW " & i).Range("A1:H200")

    .Locked = false

End with

Worksheets("KW " & i).Protect Password:="secretstuff"

Next i

The columns beyond H were never used, but some of them were accidently unlocked. This is what I tried to fix here.

No cells further than M were unlocked (I checked several times)!

This part of the code should only lock those additional columns and unlock those up to column H.

But the result is: After the code is executed, the range "N1:N201" is suddenly unlocked, although it was never effected by the code.

So I changed my code to lock the range "I1:N200" instead.

Afterwards column O was unlocked in the same way.

The easy solution was to lock the whole sheet with Worksheets("KW " & i).Cells.Locked = True and unlock the range A1:H200 afterwards.

Nevertheless I am puzzled why the other way unlocked cells I didn't even touch in the first place.

Explanation / Answer

..Hi,,,

try this.......

............@

For i = 1 To 53

With Worksheets("KW " & i)

.Unprotect Password:="secretstuff"

.Range("A1:M200").Locked = True

.Range("A1:H200").Locked = False

.Protect Password:="secretstuff"

End with

Next i

.........@

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.