Why can I not lock a cell throught code?

  • Thread starter Thread starter Jagdip Singh Ajimal
  • Start date Start date
J

Jagdip Singh Ajimal

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

--------------------------------------------------------------------------------
 
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
(e-mail address removed)
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

--------------------------------------------------------------------------
Click to expand...
------
 
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
 
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.
 
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.
 
Are you trying to do this from the same sheet you are on or a different
sheet?? On which sheet is the cell a14??
 
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
(e-mail address removed)
 
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
--------------------------------------------------------------------------------------------------------
 
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.)
 
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
 
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
 
Back
Top