Automatically Protect Excel Spreadsheet Upon Closing

[mquinn0908]

I have an Excel spreadsheet that I want to protect with a password but I don't want the users to be able to turn the protection off and then have to remember to turn it back on. What I would like is fot the spreadsheet to be automatically password protected with the user closes the file. Is there any way to accomplish this?

Mandy
MCP/A+/Network+

 

[SkipVought]

Hi,

Are you referring to a workbook password protection or individual worksheet protection?

Either way, you need a password!

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Deniall]

I like to do this sort of protecting as part of the Workbook_Open event, using something along the lines of the code below. However I cannot see any reason why it couldn't be done when the workbook is SAVED. (Merely CLOSING the file does not necessarily update the version on disk.)

Private Sub Workbook_Open()
'
'  Do the required stuff when the workbook is opened.
'
Dim WorkSht As Worksheet, ThisChart As Chart
'
'  ************************************************************************
'  Protect all worksheets & chartsheets, and the workbook itself.
'  ************************************************************************
'
PsWd = "Whatever"
'
'  Loop through all the worksheets.
'
For Each WorkSht In Worksheets
    WorkSht.Protect DrawingObjects:=True, Contents:=True, _
                    Scenarios:=True, Password:=PsWd
    '
    '  Allow user to select (but not change) locked cells.
    '  Note that with some versions of Excel this setting does not
    '  persist (ie it gets forgotten when the workbook is saved).
    '
    WorkSht.EnableSelection = xlNoRestrictions
Next WorkSht
'
'  Loop through all the charts.
'
For Each ThisChart In Charts
    ThisChart.Protect DrawingObjects:=True, Contents:=True, _
                      Scenarios:=True, Password:=PsWd
Next ThisChart
'
'  Protect the workbook itself.
'
ActiveWorkbook.Protect Structure:=True, Windows:=False, _
                       Password:=PsWd
'
End Sub

 

[mquinn0908]

This would be for just one workbook and I do want it to have a password I just want it to be where when the user closes it (saves it) that it is automatically password protected again without any user intervention.

Mandy
MCP/A+/Network+