Cell Protection vs. Worksheet Protection

[Guest]

Is there anyway to protect selected cells from editing without having to
protect the entire worksheet? I know that I can lock or unlock certain cells
when I protect the worksheet as a whole, but when I use worksheet protection,
it interferes with some of my code and user functionality.... Please
advise...

Thanks,

Kevin

 

[Ken Johnson]

Is there anyway to protect selected cells from editing without having to
protect the entire worksheet? I know that I can lock or unlock certain cells
when I protect the worksheet as a whole, but when I use worksheet protection,
it interferes with some of my code and user functionality.... Please
advise...

Thanks,

Kevin

Hi Kevin,

Change your code so that it unprotects the sheet, makes its changes,
then protects the sheet.

Eg...

Public Sub CodeForProtection()
With Sheet1
..Unprotect "ken"
..Range("A1").Value = 1
..Protect "ken"
End With
End Sub

If you are not using a password then you just use .Unprotect and
..Protect on their own.

Ken Johnson

 

[Guest]

That's the only way! However, macros can also unprotect and reprotect your
worksheet.

 

[Dave Peterson]

Depending on what your code does, you can protect the worksheet in code.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

The userinterfaceonly means that you're stopping the user from changing locked
cells on a protected worksheet (Sheet1, in my example). But your code can
change things that the user can't.

But do some testing. There are a few things that still need you to unprotect
the worksheet, do your stuff, and protect the worksheet. (But maybe you're not
doing one of those few things.)

 

[Guest]

Thanks for all your help, guys!