Macro to password protect certain cells when opening a document

[Kittysback]

Hello

I have a workbook (excel 2007) and would like to make sure that users need to enter a password when they open the file to be able to edit certain cells.

Currently without a macro, if someone has the password, they can unlock the cells and edit the document, but if they forget to protect the sheet when closing it, the document will be unprotected when the next person opens it. Even if the next person doesn't have the password they will be able to edit cells which should be locked to them.

Can someone advise a macro which would ensure that certain cells are already password protected WHENEVER the file is opened (regardless of whether or not last user protected the file before closing)?

Many thanks in advance

 

[Trevor G]

Welcome to the Forum,

You could record a macro to do the protection and once you have the code you would place that in the workbook event before close.

To record the macro select a different sheet first then use the Tools Menu and Go Down to Record Macro give the macro a name like mcrProtect then click OK, next click the sheet name and then highlight the cells you want people to edit then use Ctrl + 1 and (format Cells) then click the Protect Tab and uncheck the Hidden tick box and click OK. Next go to the Tools Menu and Protect Sheet then Add your password Take a note of it, then click Ok and then Stop Recording, this gives you the code you need.

Next to place it in the workbook event.

Use Alt + F11 to go into the VBA screen then highlight the code inside the macro and copy it, then on the left you will see below the sheet names ThisWorkBook double click this word. Then at the top click the drop down that has the word General and select Workbook this will give you a procedure stating Private Workbook On Open look at the drop down at the top right and click this to change Before Close, paste in your code. It will look something like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet4").Select 'Sheet Name
Range("A1:B3").Select 'Range of cells
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Password"
End Sub