Protecting Worksheets

S

saturnin02

Win XP HE, SP1
Excel 2002 SP3

Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE?
I dt want to have to do it manually for each sheet....
Tx,
S
 
G

Gord Dibben

S

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP
 
S

saturnin02

Gord,
Great, Tx a lot for the macro.
How do I make the adjustment in it to uncheck "Allow all users of the
worksheet to: Select Locked Cells"
(In other words, do NOT let users select locked cells.)
Tx a million.
S
 
G

Gord Dibben

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Protect Password:="justme"
.EnableSelection = xlUnlockedCells
End With
Next n
Application.ScreenUpdating = True
End Sub


Gord
 
G

Gord Dibben

David McRitchie has a "getting started" page at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Note links to other sites at bottom of David's page.

Note: Visual Basic for Applications 101 is an apparently defunct site.

Also at Tushar Mehta's site is "beyond the macro recorder"

http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

The recorder leaves a lot to be desired since it will generally create much
superfluous code for simple operations, but it is a start.

Spend some time lurking over at the microsoft.public.excel.programming
newsgroup.

Study the code provided by the regulars over there.


Gord
 
S

saturnin02

Gord said:
David McRitchie has a "getting started" page at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Note links to other sites at bottom of David's page.

Note: Visual Basic for Applications 101 is an apparently defunct site.

Also at Tushar Mehta's site is "beyond the macro recorder"

http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

The recorder leaves a lot to be desired since it will generally create
much superfluous code for simple operations, but it is a start.

Spend some time lurking over at the microsoft.public.excel.programming
newsgroup.

Study the code provided by the regulars over there.


Gord
Click to expand...

Ok. Tx again.
S
 
S

saturnin02

Gord,
I made a mistake in my description:
I get an invalid or unqualified reference from that line we added.
I am NOT selecting any specific range in the worksheet.
I just want to clear the checkbox that would normally allow selecting
unprotected cells, if any.
Any chance we can get this last twist to work?
S
 
S

saturnin02

CANCEL comments that does not work.
it DOES work; I had made a mistake in my syntax.
All good.
Tx again!
 
G

Gord Dibben

If you don't want users to select any cells, just change the line

..EnableSelection = xlUnlockedCells

to

..EnableSelection = xlNoSelection

The "selection" is not referring to a range of cells, just the ability to
select a cell.

The invalid or unqualified reference does not show up for me.


Gord
 
S

saturnin02

Gord, Sorry, it's in my previous post...I had made a mistake in cut and
paste--it was on me--u definitely had it right!
Thanks!
S
 
Top