Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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. -- Cheers, Ryan "Steve" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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. "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
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? -- 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
unable to set the locked property of the range class
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the FormulaArrary property of the range class | Excel Worksheet Functions | |||
Unable to set NumberFormat Property of Range class | Excel Programming | |||
Unable to set NumberFormat Property of Range class | Excel Programming | |||
Unable to set the Locked property of the range class | Excel Programming | |||
Unable to set the Locked Property of the Range Class | Excel Programming |