0

I need to lock multiple cells in a different protected worksheet but the same workbook after entering data by macro. Which works fine for the first data range, but it always breaks at the second range.I tried this:

ActiveWorkbook.Worksheets("Summary").Range("C3").Value = Range("B18").Value + Range("C18").Value
ActiveWorkbook.Worksheets("Summary").Unprotect Password:="password"
ActiveWorkbook.Worksheets("Summary").Protect Password:="password", UserInterFaceOnly:=True
ActiveWorkbook.Worksheets("Summary").Range("A11:B15", "C3").Locked = True

I also tried this

ActiveWorkbook.Worksheets("Summary").Unprotect Password:="password"
ActiveWorkbook.Worksheets("Summary").Protect Password:="password", UserInterFaceOnly:=True
ActiveWorkbook.Worksheets("Summary").Range("A11:B15").Locked = True
ActiveWorkbook.Worksheets("Summary").Range("C3").Value = Range("B18").Value + Range("C18").Value
ActiveWorkbook.Worksheets("Summary").Range("C3").Locked = True

Each time they stop at the Range C3. For the first script, I removed ,"C3", and it is fine; for the second, I removed the whole line, it works. But i really need to lock C3 from editing. Any suggestions for how to do it? The Error message is always Run-time error '1004': Unable to set the locked property of the Range class.

Community
  • 1
  • 1
yoyo
  • 31
  • 2
  • 6
  • 1
    Is C3 a merged cell by any chance? If so, check [this question](https://stackoverflow.com/questions/9184818/error-1004-when-setting-range-locked) – Excelosaurus Oct 16 '17 at 21:03
  • Thanks, Excelosaurus. I just saw your comment. Don't know why I didn't notice it. Yes, C3 is a merged cell and I put solved it now. – yoyo Oct 17 '17 at 18:52

1 Answers1

0

I'd recommend checking this question out.

Using the .Lock turns the lock flag on or off, once the sheet is protected you cannot edit any cells that have the lock flag turned on.

You'll need to do...

ActiveWorkbook.Worksheets("Summary").Range("C3").Value = Range("B18").Value + Range("C18").Value
ActiveWorkbook.Worksheets("Summary").Range("A11:B15", "C3").Locked = True
ActiveWorkbook.Worksheets("Summary").Protect Password:="password", UserInterFaceOnly:=True

EDIT 1:

ActiveWorkbook.Worksheets("Summary").Unprotect Password:="password"
ActiveWorkbook.Worksheets("Summary").Range("C3").Value = Range("B18").Value + Range("C18").Value
ActiveWorkbook.Worksheets("Summary").Range("A11:B15", "C3").Locked = True
ActiveWorkbook.Worksheets("Summary").Protect Password:="password", UserInterFaceOnly:=True
Maldred
  • 1,074
  • 4
  • 11
  • 33
  • I corrected the error of the first script. I did unprotect the worksheet before I can lock anything. – yoyo Oct 16 '17 at 21:15
  • You have to protect the sheet **AFTER** the cell is locked, please see my edit – Maldred Oct 16 '17 at 21:33
  • Thanks, Maldred. But the whole worksheet is protected as UserInterFaceOnly, which allows macro to edit and lock cells. – yoyo Oct 17 '17 at 14:39
  • I figured out why. The C3 is a merged cell. Now I use ActiveWorkbook.Worksheets("Summary").Range( "C3").MergeArea.Locked = True Now the problem is solved. – yoyo Oct 17 '17 at 14:39
  • Not sure how I missed that part... but good to know you found the solution! Might want to add the answer to this thread – Maldred Oct 17 '17 at 15:23