protect multiple sheets at one time with options
 

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

THANK YOU! that worked great on the unlocked cells can i apply this to locked
cells as well? i have some cells locked so they can not alter the formulas

Once again Michael i appreciate all your time.

"Michael" wrote:

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

I tested the code in my Excel 2003 and it works with both locked and unlocked
cells, If you cannot confirm, try getting one of the Excel 2003 users to test
it for you.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

THANK YOU! that worked great on the unlocked cells can i apply this to locked
cells as well? i have some cells locked so they can not alter the formulas

Once again Michael i appreciate all your time.

"Michael" wrote:

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

i have the cells locked so they cannot select them. i think this is the
problem. can i add some more code to allow them to select locked cells? but
still not alter the formulas

"Michael" wrote:

I tested the code in my Excel 2003 and it works with both locked and unlocked
cells, If you cannot confirm, try getting one of the Excel 2003 users to test
it for you.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

THANK YOU! that worked great on the unlocked cells can i apply this to locked
cells as well? i have some cells locked so they can not alter the formulas

Once again Michael i appreciate all your time.

"Michael" wrote:

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

The following statement should allow cell selection without users being able
to change the contents.
ws.EnableSelection = xlSelection
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have the cells locked so they cannot select them. i think this is the
problem. can i add some more code to allow them to select locked cells? but
still not alter the formulas

"Michael" wrote:

I tested the code in my Excel 2003 and it works with both locked and unlocked
cells, If you cannot confirm, try getting one of the Excel 2003 users to test
it for you.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

THANK YOU! that worked great on the unlocked cells can i apply this to locked
cells as well? i have some cells locked so they can not alter the formulas

Once again Michael i appreciate all your time.

"Michael" wrote:

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

Awsome! Thanks soooo much. that saves a lot of time going page to page and
workbook to workbook
Once again THANK YOU VERY MUCH.
"Michael" wrote:

The following statement should allow cell selection without users being able
to change the contents.
ws.EnableSelection = xlSelection
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have the cells locked so they cannot select them. i think this is the
problem. can i add some more code to allow them to select locked cells? but
still not alter the formulas

"Michael" wrote:

I tested the code in my Excel 2003 and it works with both locked and unlocked
cells, If you cannot confirm, try getting one of the Excel 2003 users to test
it for you.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

THANK YOU! that worked great on the unlocked cells can i apply this to locked
cells as well? i have some cells locked so they can not alter the formulas

Once again Michael i appreciate all your time.

"Michael" wrote:

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg

protect multiple sheets at one time with options
 

Sorry to bother you again.
all the code works great! but i just noticed that my file sizes went from
316-418kbs to 2.6-5.7meg is there some housekeeping i need to do?

"Greg" wrote:

Awsome! Thanks soooo much. that saves a lot of time going page to page and
workbook to workbook
Once again THANK YOU VERY MUCH.
"Michael" wrote:

The following statement should allow cell selection without users being able
to change the contents.
ws.EnableSelection = xlSelection
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have the cells locked so they cannot select them. i think this is the
problem. can i add some more code to allow them to select locked cells? but
still not alter the formulas

"Michael" wrote:

I tested the code in my Excel 2003 and it works with both locked and unlocked
cells, If you cannot confirm, try getting one of the Excel 2003 users to test
it for you.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

THANK YOU! that worked great on the unlocked cells can i apply this to locked
cells as well? i have some cells locked so they can not alter the formulas

Once again Michael i appreciate all your time.

"Michael" wrote:

Try this:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ws.Protect Password:=""
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Next ws
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

Thanks for the fast response!
i'm using Office 07 but users are 03
when i inserted your code i was not able to change any formatting. i may
have put it in the wrong place.

here is the code i'm using now:

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:=""
Next ws

"Michael" wrote:

If you are using Excel 2003 use the following statement:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This is not allowed in lower Excel Versions.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Greg" wrote:

i have 50+ pages to protect. i have the code to protect and unprotect all the
pages at once.
my question is:
How can i add code so the user to change font, color, etc.. in these
protected sheets?


any and all help would greatly be appreciated
Many thanks in advance.

Greg