protecting all sheets in a workbook

trf

Board Regular
Joined
Apr 17, 2002
Messages
62
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
This will protect each sheet with the password ps:

Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 15:06
 
Upvote 0
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
 
Upvote 0
Or you can set up a second workbook to interact with the one in question. Insert this:

Code:
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...
 
Upvote 0
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
:)
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top