unable to set the locked property of the range class

[Steve]

Morning all.
Using some code from Ryan H. on another post, from yesterday, I've set up my
password protection routine, and am running across a 1004 error.
I keep getting a "unable to set the locked property of the range class."
In reading more on this, it appears that merged cells are my primary culprit
(of which I have many).
While I can remove some, some are necessary due to the operations being
performed with other tasks.
Is there any kind of a work around for this, so it will still perform the
cell/range locking?

Thank you.

 

[Ryan H]

Ahh...merged cells. Working with merged cells can sometimes be difficult,
but possible. What code did I help you with? Please post all the code and
specify where you are getting the error.

 

[Steve]

Sub LockRows()

Dim wks As Worksheet
Dim LastRow As Long
Dim rw As Long

For Each wks In Worksheets
With wks
.Unprotect Password:="MyPass"
.Cells.Locked = False
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For rw = 2 To LastRow
If Trim(.Cells(rw, "A").value) <> "" Then
.Rows(rw).Locked = True
End If
Next rw
.Protect "MyPass", DrawingObjects:=False, Contents:=True,
Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows :=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting _
:=True, AllowFiltering:=True
'.Protect Password:="MyPass"
End With
Next wks
End Sub

 

[Steve]

I'm sorry, I got a phone call I'd needed to take, and posted this before I
was done.
you can slap my hand now....
Where the error arises is at:

..Rows(rw).Locked = True

 

[Ryan H]

Try this code. This code will test if the cell being analyzed is merged with
other cells, and if there is something in the merged area it will lock all
rows in that merged area. Hope this helps! If so, let me know, click "YES"
below.

Option Explicit

Sub LockRows()

Dim wks As Worksheet
Dim LastRow As Long
Dim rw As Long
Dim MyRange As Range

For Each wks In Worksheets
With wks
.Unprotect Password:="MyPass"
.Cells.Locked = False
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For rw = 2 To LastRow
Set MyRange = .Cells(rw, "A").MergeArea
If MyRange.Rows.Count = 1 Then
If Trim(.Cells(rw, "A").Value) <> "" Then
.Rows(rw).Locked = True
End If
Else
If Trim(MyRange.Value2(1, 1)) <> "" Then
MyRange.EntireRow.Locked = True
End If
End If
Next rw
.Protect "MyPass", DrawingObjects:=False, Contents:=True,
Scenarios:= _
True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True,
AllowFiltering:=True
'.Protect Password:="MyPass"
End With
Next wks

End Sub

 

[Steve]

Hi again Ryan,
Ok, it throws the error at the same location in the code, and at the same
row as before.
The first 7 cells (columns) on that row are unmerged, but the 8th column
does have merging in the 6th and 7th rows.
Hence my issue.... there are near 800 workbooks and not all are exactly
identical. While I am bringing each workbook that I do work on into
compliance to being identical, I'm trying to do work arounds where they're
exhibiting minor differences.

Again, thank you for your helps.

 

[Steve]

In running through this more, I have noticed something that will cause
trouble in the future.
It appears to be locking the entire row. Is there some way that I can get it
to only lock a given number of colums for the row being locked?
If this is too far an aside, let me know, and I'll post under a new heading.
Again-- thank you.

 

[Ryan H]

Is it possible for you to send me a copy of your workbook? I need to see how
your cells are merged instead of me guessing. Plus, which columns do you
want locked?

 

[Ryan H]

e-mail: (e-mail address removed)
--
Cheers,
Ryan

In running through this more, I have noticed something that will cause
trouble in the future.
It appears to be locking the entire row. Is there some way that I can get it
to only lock a given number of colums for the row being locked?
If this is too far an aside, let me know, and I'll post under a new heading.
Again-- thank you.