Why can I not lock a cell throught code?

Jagdip Singh Ajimal · Apr 25, 2005

I have some code, whereby I am trying to lock a cell. I keep getting
the message:

Runtime error '1004':
Unable to set the Locked property of the Range class.

I dont know why this is happening. Can anyone help? Here is a sample
of my code:

------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
CalculateDates
End Sub

Private Sub CalculateDates()
'Application.ActiveWorkbook.Worksheets("Sheet1").Active
Application.ActiveWorkbook.Worksheets("Sheet1").Unprotect
Application.ActiveWorkbook.Worksheets("Sheet1").EnableSelection =
xlNoRestrictions
'Application.ActiveWorkbook.Worksheets("Sheet1").ActiveCell.Activate

If Range("AM50").Value = "Simple" Then
Range("AM56").Locked = True
''''''
ElseIf Range("AM50").Value = "Complex" Then
Range("AM56").Locked = True
''''''
Else
Range("AM56").Locked = False
'''''''
End If

Application.ActiveWorkbook.Worksheets("Sheet1").Protect
Contents:=True, UserInterfaceOnly:=True
End Sub

--------------------------------------------------------------------------------

Don Guillett · Apr 25, 2005

try this from a regular sub
Sub cd()
Worksheets("Sheet1").Unprotect
Range("b14").Locked = False
If UCase(Range("a14")) = "AAA" _
Or UCase(Range("a14")) = "BBB" _
Then Range("b14").Locked = True
Worksheets("Sheet1").Protect _
Contents:=True, UserInterfaceOnly:=True
End Sub

or this from the active sheet
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Address <> "$A$14" Then Exit Sub
Range("b14").Locked = False
If UCase(Target) = "AAA" _
Or UCase(Target) = "BBB" _
Then Range("b14").Locked = True
ActiveSheet.Protect _
Contents:=True, UserInterfaceOnly:=True
End Sub

--
Don Guillett
SalesAid Software
[email protected]

Jagdip Singh Ajimal said:
I have some code, whereby I am trying to lock a cell. I keep getting
the message:

Runtime error '1004':
Unable to set the Locked property of the Range class.

I dont know why this is happening. Can anyone help? Here is a sample
of my code:

-------------------------------------------------------------------------- ----
Private Sub Worksheet_Change(ByVal Target As Range)
CalculateDates
End Sub

Private Sub CalculateDates()
'Application.ActiveWorkbook.Worksheets("Sheet1").Active
Application.ActiveWorkbook.Worksheets("Sheet1").Unprotect
Application.ActiveWorkbook.Worksheets("Sheet1").EnableSelection =
xlNoRestrictions
'Application.ActiveWorkbook.Worksheets("Sheet1").ActiveCell.Activate

If Range("AM50").Value = "Simple" Then
Range("AM56").Locked = True
''''''
ElseIf Range("AM50").Value = "Complex" Then
Range("AM56").Locked = True
''''''
Else
Range("AM56").Locked = False
'''''''
End If

Application.ActiveWorkbook.Worksheets("Sheet1").Protect
Contents:=True, UserInterfaceOnly:=True
End Sub

--------------------------------------------------------------------------

------

JE McGimpsey · Apr 25, 2005

Probably because you're trying to manipulate the activesheet (which is
not Sheet1) when it's locked. Failing to qualify

Range("AM56")

uses the ActiveSheet rather than Sheet1. Try:

Public Sub CalculateDates()
With Application.ActiveWorkbook.Worksheets("Sheet1")
.Unprotect
.EnableSelection = xlNoRestrictions
If .Range("AM50").Value = "Simple" Then
.Range("AM56").Locked = True
''''''
ElseIf .Range("AM50").Value = "Complex" Then
.Range("AM56").Locked = True
''''''
Else
.Range("AM56").Locked = False
'''''''
End If
.Protect Contents:=True, UserInterfaceOnly:=True
End With
End Sub

jsa1981 · Apr 25, 2005

Sorry, gives a different error this time:

Runtime Error '1004':
Application defined or object defined error.

I have no idea what is wrong here. I havent protected the sheet yet so
there should not be any problems.

jsa1981 · Apr 25, 2005

My workbook/sheet is not locked yet.

The problem is still there:

Sub CalculateDates()

With Application.ActiveWorkbook.Worksheets("Sheet1")
.Unprotect
.EnableSelection = xlNoRestrictions
.Range("AM56").Locked = False ''<<<------

My CalculateDates() Sub uses the Range() functions all the way through
with a problem. As soon as I try to manipulate the Locked property of a
cell, my workbook crashes.

Don Guillett · Apr 25, 2005

Are you trying to do this from the same sheet you are on or a different
sheet?? On which sheet is the cell a14??

William · Apr 26, 2005

I think you can do one of two things:-

Change the worksheet event to:-

Private Sub Worksheet_Change(ByVal Target As Range)
Run CalculateDates
End Sub

or remove "Private" from the sub

Sub CalculateDates()
Application.ActiveWorkbook.Worksheets("Sheet1").Unprotect
Application.ActiveWorkbook.Worksheets("Sheet1").EnableSelection =
xlNoRestrictions
If Range("AM50").Value = "Simple" Then
Range("AM56").Locked = True
ElseIf Range("AM50").Value = "Complex" Then
Range("AM56").Locked = True
Else
Range("AM56").Locked = False
End If
Application.ActiveWorkbook.Worksheets("Sheet1").Protect Contents:=True,
UserInterfaceOnly:=True
End Sub

--

XL2003
Regards

William
[email protected]

jsa1981 · Apr 26, 2005

Ok, here is the full code. I have only one sheet in the workbook.
I have striped the code down to the following.
It is a little confusing as there are three AM?? cells, but ...
AM15 = a Date cell with date validation
AM50 = a List validation cell
AM56 = the cell I want to lock/unlock & have date validation on.
All three cells have been merged with other cells.

I create an empty workbook and tried a simplified version on this, and
it worked perfectly. I have no idea why it does not work here.

The error I get at the moment is:
Runtime Error '1004':
Unable to set the Locked property of the Range class.

------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ActiveWorkbook.Worksheets("Sheet1").Unprotect
Application.ActiveWorkbook.Worksheets("Sheet1").EnableSelection =
xlNoRestrictions

Dim CalDate As Date

If Range("AM50").Value = "Simple" Then
Range("AM56").Locked = True
If Range("AM15").Value <> "" Then
CalDate = DateAdd("d", 15, CDate(Range("AM15")))
If DateDiff("d", CalDate, CDate(Range("AM56"))) <> 0 Then
Range("AM56").Value = CalDate
End If
End If
ElseIf Range("AM50").Value = "Complex" Then
Range("AM56").Locked = True
If Range("AM15").Value <> "" Then
CalDate = DateAdd("d", 20, CDate(Range("AM15")))
If DateDiff("d", CalDate, CDate(Range("AM56"))) <> 0 Then
Range("AM56").Value = CalDate
End If
End If
Else
Range("AM56").Locked = False
If Range("AM56").Value <> "" Then
Range("AM56").Value = ""
End If
End If

Application.ActiveWorkbook.Worksheets("Sheet1").Protect
Contents:=True, UserInterfaceOnly:=True

End Sub
--------------------------------------------------------------------------------------------------------

jsa1981 · Apr 26, 2005

GOOD NEWS. I found it.

I got it from this page ...
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21359220.html

It turns out that VBA doesnt like merged cells. As I have merged 4
cells for AM56, I have to use:

Range("AM56:AQ56").Locked = True

Dave Peterson · Apr 26, 2005

Another reason to hate merged cells.

But you may want to use something like:

Option Explicit
Sub testme()
With ActiveSheet
.Range("am56").MergeArea.Locked = True
End With
End Sub

Then you don't need to know how big the mergearea is. (And it won't hurt if
that cell isn't merged.)

JE McGimpsey · Apr 26, 2005

All three cells have been merged with other cells.

There's your main problem. You can't change the .Locked value of only
one merged cell.

Using

Range("AM56").MergeArea.Locked = True

should work.

Also note that when you change the value of AM56, you're causing the
Worksheet_Change event to fire again. This should work a bit more
efficiently:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CalDate As Date
Dim sType As String
On Error GoTo ErrHandler
Me.Unprotect
Me.EnableSelection = xlNoRestrictions
Application.EnableEvents = False
sType = Range("AM50").Value
With Range("AM56").MergeArea
If sType = "Simple" Or sType = "Complex" Then
.Locked = True
CalDate = CDate(Range("AM15").Value)
If CalDate <> 0 Then _
.Value = CalDate + 15 - (5 * (sType = "Complex"))
Else
.Locked = False
.ClearContents
End If
End With
ErrHandler:
Application.EnableEvents = True
Me.Protect Contents:=True, UserInterfaceOnly:=True
End Sub

jsa1981 · Apr 26, 2005

RE: Dave Peterson

Thanks for that. I may not be able to change this version now (I've
already completed it and passed it on), but I will remember that for
next time.

RE: JE McGimpsey

I actually solved this by testing to see if the value needed changing
first (see below). My first attempt at this got me into an infinite
loop lol.

If Range("AM56").Value <> myValue Then
Range("AM5").Value = myValue
End If

Why can I not lock a cell throught code?

Jagdip Singh Ajimal

Don Guillett

JE McGimpsey

jsa1981

jsa1981

Don Guillett

William

jsa1981

jsa1981

Dave Peterson

JE McGimpsey

jsa1981