Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA unlock and lock cell

Posted on 2013-12-11
7
Medium Priority
?
536 Views
Last Modified: 2013-12-11
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
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39711258
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
 

Author Comment

by:Frank Freese
ID: 39711273
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 39711285
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Frank Freese
ID: 39711303
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39711322
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
 

Author Closing Comment

by:Frank Freese
ID: 39711324
thanjs - I failed to see that I did not include the :
Apprciate it - on to next problem
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39711344
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question