About Us

Ryan H

e-mail:
--
Cheers,
Ryan

"Steve" wrote:

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" wrote:

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
--
Cheers,
Ryan

"Steve" wrote:

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

"Steve" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Unable to set the FormulaArrary property of the range class	RTK	Excel Worksheet Functions	0	April 13th 10 08:38 AM
Unable to set NumberFormat Property of Range class	Gary''s Student	Excel Programming	0	March 28th 07 02:43 AM
Unable to set NumberFormat Property of Range class	Ade	Excel Programming	0	March 28th 07 12:59 AM
Unable to set the Locked property of the range class	Stuart[_5_]	Excel Programming	5	June 25th 04 03:32 PM
Unable to set the Locked Property of the Range Class	Stuart[_5_]	Excel Programming	0	July 15th 03 06:59 PM

Menu

About Us