Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.

Hi,

try this

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

'Do lots of things

Application.EnableEvents = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="mypassword"


Mike

"Dana M" wrote:

Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.

You can not add a line break in VBA without adding a line break character
which is the underscore. I Like to line up my parameters for easy reading so
I do it something like this. You have one but you have 2 line breaks. That
might just be how you post got formatted though. Other than that you need a
coma between each parameter and you are missing one after password...

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True
--
HTH...

Jim Thomlinson


"Dana M" wrote:

Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.

Code you pasted is missing a comma after "mypassword" and a
line-continuation mark.

Try this................

ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

Why have you disabled events then reenabled immediately or is there some
code left out?


Gord Dibben MS Excel MVP

On Wed, 11 Feb 2009 12:19:01 -0800, Dana M
wrote:

Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.

Thanks, Gord! Yes, there is missing code inbetween, however, the events is a
worksheet event that runs separate from my macros - I learned about that
through these posts also - it was causing a bug in the macro - I played
around with the code and added the disable at the beginning of my macro and
enable at the end.

"Gord Dibben" wrote:

Code you pasted is missing a comma after "mypassword" and a
line-continuation mark.

Try this................

ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

Why have you disabled events then reenabled immediately or is there some
code left out?


Gord Dibben MS Excel MVP

On Wed, 11 Feb 2009 12:19:01 -0800, Dana M
wrote:

Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.

Thanks Mike! Truly appreciated. Clear and helpful.

"Mike H" wrote:

Hi,

try this

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

'Do lots of things

Application.EnableEvents = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="mypassword"


Mike

"Dana M" wrote:

Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.

Thanks so much, Jim. This really helps. You experts are making me look
good!!!

"Jim Thomlinson" wrote:

You can not add a line break in VBA without adding a line break character
which is the underscore. I Like to line up my parameters for easy reading so
I do it something like this. You have one but you have 2 line breaks. That
might just be how you post got formatted though. Other than that you need a
coma between each parameter and you are missing one after password...

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True
--
HTH...

Jim Thomlinson


"Dana M" wrote:

Using info obtained from other postings in this chat, I am attempting to add
a password to my sheet protection. I have macros that add and delete rows in
the worksheet. I have 'Unprotect' code at the beginning of the macros and
'Protect" at the end, which I modified slightly in this way:

ActiveSheet.Unprotect Password:="mypassword"
Application.EnableEvents = False

Application.EnableEvents = True
ActiveSheet.Protect Password:="mypassword" DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

I haven't even gotten to the point of trying to step through the macro, but
the 'unprotect' statement (is that what you call that bit of code,
statement?)
remained in black text, but the 'protect' statement immediately turned red
with an error message, "Compile error - expected end of statement" right
around the DrawingObjects:=True part.

This all worked correctly before adding the password, and before adding the
Password:="mypassword" to the code.