• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
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).
0
 
Frank FreeseAuthor Commented:
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
0
 
Rory ArchibaldCommented:
You forgot the colons:

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

Open in new window


Otherwise you are passing an expression rather than a named argument.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Frank FreeseAuthor Commented:
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

0
 
Rob HensonFinance AnalystCommented:
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
0
 
Frank FreeseAuthor Commented:
thanjs - I failed to see that I did not include the :
Apprciate it - on to next problem
0
 
Rob HensonFinance AnalystCommented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now