RE: Protecting sheets

Hi David,

Thank you for your question. This question is outside the scope of the forum but I am happy to give you the code and an explanation.

You need some VBA code to achieve this:

Dim shtAnySheet As Worksheet
Dim sPassword As String
sPassword = "dog"
Set shtAnySheet = Worksheets("Sheet1")
shtAnySheet.Protect sPassword
End Sub

The code above protects the sheet in the active workbook called Sheet1, with a password of dog. As the code has been entered into the Workbook_BeforeClose event, then this code will run each time the workbook is closed.

Open your workbook and use ALT + F11 to open the VBA window. Make sure you have the Project Explorer window open(side window with VBA Projects listed).

Find the VBA project relating to your workbook and expand it out. Double click on 'This Workbook'. In the right hand window that appears, from the left drop down box that says 'General', select Workbook. From the second drop down on the right hand side choose the 'BeforeClose' Event.

This should create a sub procedure with end sub at the end(Private Sub Workbook_BeforeClose(Cancel As Boolean)). Copy the code in this email and paste it in between the Private Sub and End sub lines. Then close and save Excel. You must save the workbook using Save As and save it as a Macro Enabled workbook. When you open the file, you must click Enable content for the macro to run. Obviously, replace the word dog with your own password.

I have attached a screenshot showing the VBA code and window.

I hope this answers your question.

Regards

Simon

Attached files...

VBA Screenshot.doc

RE: Protecting sheets

Hi David,

Thank you for your response.

This would require some more VBA code being developed which will be outside the scope of the forum.

However please find the following links that may help you build the code.

http://www.ozgrid.com/VBA/autofilter-vba.htm
http://www.contextures.com/xlautofilter03.html

I hope this helps.

Regards

Simon