asked on

VBA unlock and lock cell

Folks,
I have a need to unlock a range (B3:C3), clear the cells and then lock them. What I've tried below fails so a little tweaking here is appreciate:

DetermineLastUseCell.Range("B3:C3").Locked = False
Worksheets("DetermineLastUseCell").Range("B3:C3").Clear
Worksheets("DetermineLastUseCell").Range("B3:C3").Locked = True

Open in new window

Thanks

Rory Archibald

If the sheet is protected you need to unprotect it first, then you can just clear the cells and reprotect the sheet. (no need to change the Locked property of the cells).

Frank Freese

ASKER

I forgot to metion tha tthe sheet is protected:

ActiveSheet.Unprotect Password = "123memphis"
Range("B3:C3").Select
Selection.Locked = False
Worksheets("DetermineLastUseCell").Range("B3:C3").Clear
Range("B3:C3").Select
Selection.Locked = True
ActiveSheet.Protect Password = "123memphis"

Open in new window

Still can't get by the first line of code

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Freese

ASKER

Folks,
I've changed my code to this. My objective is unportect the worksheet from B3:C3, clear B3:C3, redraw the cell B3 then protect the worksheet again.

ActiveSheet.Unprotect Password = "123memphis"
Range("B3:C3").Select
Worksheets("DetermineLastUseCell").Range("B3:C3").Clear
Dim rng As Range
   Set rng = Range("B3")
    With rng.Borders
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With
ActiveSheet.Protect Password = "123memphis"

Open in new window

Rob Henson

If you are only wanting to clear the contents of the cell use:

Range("B3:C3").ClearContents

You won't then have to redo the borders on B3.

Thanks
Rob H

Frank Freese

ASKER

thanjs - I failed to see that I did not include the :
Apprciate it - on to next problem

Rob Henson

So you would end up with

ActiveSheet.Unprotect Password:="123memphis"
Range("B3:C3").ClearContents
ActiveSheet.Protect Password:="123memphis"

Open in new window

The colons that rorya was referring to were after the word Password.

Thanks
Rob H