protect multiple sheets at one time with options

G

Greg

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
 
M

Michael

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.
 
G

Greg

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
 
M

Michael

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.
 
G

Greg

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.
 
M

Michael

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.
 
G

Greg

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
 
M

Michael

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.
 
G

Greg

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

Greg

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?
 
Top