Cell/worksheet protection with macros enabled

[Zilla]

I'm using a VBA macro courtesy of Debra D. on this NG
to temproraily resize the column for a drop-down list. All
work fine.

Now I've lock some of these cells that this macro runs
on, and enabled sheet protection on "Contents" only.
Now when I use my spreadsheet, I get an Run-time
Error 1004 - Unable to set column width property of
the range class. I've tried protection with either "Objects"
or "Scenarios" only checked, to no avail.

I gues this means I can not run macros that muck with
cell "properties" with sheet protection?

 

[JLatham]

You can unprotect the sheet incode, do your processing then put it back into
protected mode. Code something like this:

Sub PasswordsInCode()
ActiveSheet.Unprotect Password:="sheetpasswordhere"
' your processing here
ActiveSheet.Protect Password:="sheetpasswordhere", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End Sub

 

[Zilla]

Thanks. if the sheet is NOT password protected, will these be the
3 lines I would need to put in-line with existing code?

DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

Is that last line also "=True._" or really "=True" as you've typed it.

-Zilla

 

[Zilla]

Also, how can I just process the "unlocked" cells? IOW,
if cell is locked, don't run your code!

-Zilla

 

[Zilla]

I found how to check for locked cells, but how do I
negate the check? Like

If !Target.Cells.Locked Then

 

[Zilla]

Silly me...

if Target.Cells.Locked = False Then

 

[JLatham]

Those three lines are continuations of the .Protect statement, so don't leave
them in there all by their lonesome. You can determine exactly what
parameters you HAVE to have when protecting the sheet again by unprotecting
the sheet, then recording a macro to protect it with the options you want
set/not set and then look at the macro created. It will not record the
password you provide, you just stick that in at the beginning of the created
code as I did in the example.

Yes,
if Target.Cells.Locked = False Then
which is a straight-forward (and more easily understood) way of saying
If Not (Target.Cells.Locked = True) Then
but both achieve the same result.

You can 'shorthand' it this way also:
If Not(Target.Cells.Locked) Then
....

 

[Zilla]

Thanks!

Those three lines are continuations of the .Protect statement, so don't leave
them in there all by their lonesome. You can determine exactly what
parameters you HAVE to have when protecting the sheet again by unprotecting
the sheet, then recording a macro to protect it with the options you want
set/not set and then look at the macro created. It will not record the
password you provide, you just stick that in at the beginning of the created
code as I did in the example.

Yes,
if Target.Cells.Locked = False Then
which is a straight-forward (and more easily understood) way of saying
If Not (Target.Cells.Locked = True) Then
but both achieve the same result.

You can 'shorthand' it this way also:
If Not(Target.Cells.Locked) Then
...