Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 489
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 489
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 489
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 489
Default unable to set the locked property of the range class

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 05:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"