Cell/worksheet protection with macros enabled
 

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

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" wrote:

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

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

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

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

-Zilla

"Zilla" wrote in message
...
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

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

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

If !Target.Cells.Locked Then


"Zilla" wrote in message
...
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

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

Silly me...

if Target.Cells.Locked = False Then


"Zilla" wrote in message
...
I found how to check for locked cells, but how do I
negate the check? Like

If !Target.Cells.Locked Then


"Zilla" wrote in message
...
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

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

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" wrote:

Silly me...

if Target.Cells.Locked = False Then


"Zilla" wrote in message
...
I found how to check for locked cells, but how do I
negate the check? Like

If !Target.Cells.Locked Then


"Zilla" wrote in message
...
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

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

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?

--
- Zilla
(Remove XSPAM)

Cell/worksheet protection with macros enabled
 

Thanks!

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

Silly me...

if Target.Cells.Locked = False Then


"Zilla" wrote in message
...
I found how to check for locked cells, but how do I
negate the check? Like

If !Target.Cells.Locked Then


"Zilla" wrote in message
...
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

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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" wrote:

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?

--
- Zilla
(Remove XSPAM)