If-Then-Else code that Locks / Unlocks cells keeps failing

Question

Using: Excel 2010

This portion of my code keeps failing and I am not sure why. When I try running the entire code block, it returns "Run-time error '1004': Unable to set the Locked property of the Range class".

'Lock/unlock issue
ThisWorkbook.Sheets("Dashboard").Activate
    ActiveSheet.Unprotect Password:="my password"
    Selection.Locked = False
    Selection.FormulaHidden = False
    If Range("D20").Value <> "Document Recorded" Then Range("F24").Locked = True Else Range("F24").Locked = False
    ActiveSheet.Protect Password:="my password", DrawingObjects:=True, Contents:=True, Scenarios:=True

Basically, I am trying to say: If cell D20 does not equal "Document Recorded" then lock cell F24, otherwise unlock cell F24.

If the sheet is protected then you need to unprotect it to change the "Locked" status — Tim Williams, Jun 20 '16 at 18:53
@TimWilliams I am (or at least, I believe I am) unlocking the worksheet first. Let me amend my post to include the block of code. I greatly appreciate your feedback. — Chase, Jun 20 '16 at 18:55
If you try unprotecting the cell manually, do you still get an error? If so then it's a simple matter of the sheet being protected. — Chris Melville, Jun 20 '16 at 18:58
You may also be running in to conflicts by relying on `Selection` object. Are you sure that the `Selection` is what you think it is? — David Zemens, Jun 20 '16 at 18:59
this code works as is for me. I tested both cases. You can definitely make it more efficient by working directly with the sheet / range objects and removing the selects. — Scott Holtzman, Jun 20 '16 at 19:01
I don't know if you can do single line `If` statements. Break that up into a normal `If()` block (adding `End If`) and see if that works. I don't have the chance to test right now... — BruceWayne, Jun 20 '16 at 19:07
@BruceWayne - I can save you the time. The `IF` statement is good as is. — Scott Holtzman, Jun 20 '16 at 19:09

score 6 · Accepted Answer · edited May 23 '17 at 12:16

6

While I tested your code and it worked as is, I suggest refactoring your code to the following.

With ThisWorkbook.Sheets("Dashboard")

    .Unprotect Password:="my password"
    .Range("F24").Locked = .Range("D20").Value <> "Document Recorded"
    .Protect Password:="my password", DrawingObjects:=True, Contents:=True, Scenarios:=True

End With

Working directly with the objects and avoiding 'Select' and 'ActiveSheet/Workbook/Cell' is best practice and if used can lead to all kinds of unsuspecting issues.

edited May 23 '17 at 12:16

Community

1
1

answered Jun 20 '16 at 19:08

Scott Holtzman

27,099
5
37
72

3

Also using boolean expressions (as you've done) in place of `If/Then/Else` statements usually yields more concise code. – David Zemens Jun 20 '16 at 19:09
4

I like how you did the `.locked` statement, pretty snazzy. – BruceWayne Jun 20 '16 at 19:12
Wow, I agree, really like the boolean expression. Seems like it'd be more efficient than the if/then/else statement too. This worked for me, thank you! – Chase Jun 20 '16 at 20:13

score 1 · Answer 2 · answered Jun 20 '16 at 18:57

1

You have to turn the sheet protection off before you run the code. I'm assuming your using protection otherwise you wouldn't be using the cell lock feature.

answered Jun 20 '16 at 18:57

Darrell H

1,876
1
9
14

Thanks for your feedback, Darrell. You are correct about Protecting the worksheet, but I am unlocking the worksheet prior to that code snippet being run, I believe. I edited my post to include the entire code block. Maybe the issue is found there. – Chase Jun 20 '16 at 18:59
It worked for me, but the only unknown is what cell/object you are selecting with Selection.Locked=False. Once you activate the sheet the code you are using whatever selection you ended up with last time you accessed the sheet. – Darrell H Jun 20 '16 at 19:14

If-Then-Else code that Locks / Unlocks cells keeps failing

2 Answers2