Protecting all sheets (Excel XP)

Topic

New Reply

I have recorded a macro to protect a single sheet : ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

What code do I add to get it to protect all sheets in the workbook ?
Something like:

For each sheet in the workbook
Protect using the current options for each sheet
Next sheet

Reply | Quote

Replies

Something like the following (untested):

Dim oSheet as Worksheet
    For Each oSheet in Worksheets
        oSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next oSheet

Reply | Quote

Thanks Legare, works like a charm.

It saves a lot of time. I have about 20 sheets in the workbook, and to make changes, it takes forever to unprotect then protect each sheet individually.
I added a second macro to unprotect (oSheet.Unprotect), and now I can turn off my coputer and go to bed with a smile on my face…

Good work.

Reply | Quote

Thanks Legare, works like a charm.

It saves a lot of time. I have about 20 sheets in the workbook, and to make changes, it takes forever to unprotect then protect each sheet individually.
I added a second macro to unprotect (oSheet.Unprotect), and now I can turn off my coputer and go to bed with a smile on my face…

Good work.

Reply | Quote

Something like the following (untested):

Dim oSheet as Worksheet
    For Each oSheet in Worksheets
        oSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next oSheet

Reply | Quote