protecting all sheets in a workbook

[trf]

Hi - I'd like to write a macro to protect all the sheets in a workbook (in Excel 97). I don't want to protect the workbook itself, as I have found that that makes WinZip unable to compress the file by more than about 5% (so whenever I send my 5 linked files over email to a client, it would take 28M instead of a mere 8M). I'm just learning VBA and imagined that I might be able to use a method like Worksheets.Protect, but Protect is not valid for the Worksheets object. My next thought is to write a loop that would Protect each sheet... but I haven't quite got to that point of understanding VBA. Is this indeed the way? Can someone suggest code?

Thanks,
Rob

[NateO]

This will protect each sheet with the password ps:

Sub alShts2()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect "ps" 'change ps to your password
'change 'protect' to 'unprotect' to unprotect each sheet
Next ws
End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-30 15:06 ]

[trf]

That works great - thanks! But (and I should have thought of this before) now if the user knows anything about VBA, s/he can go into the VBE and learn the password. Is there any way to hide the module from public view so as to hide the password?

Thanks again,
Rob

[Tom Schreiner]

You can lock your Project in tools, project prop., protection...

[NateO]

Or you can set up a second workbook to interact with the one in question. Insert this:

ActiveWindow.ActivateNext

At the beginning and end of the code, having only the workbooks in question open.

Either way Xl's not the most secure platform...

[nisht]

download the file protect sheet from my website..

http://www.pexcel.com/download.htm

nishith desai
http://www.pexcel.com

[Chris Davison]

On 2002-04-30 15:01, trf wrote:
I don't want to protect the workbook itself, as I have found that that makes WinZip unable to compress the file by more than about 5%

Rob

I never knew that !

thanks
Chris