PDA

View Full Version : Solved: password policy when opening an excel sheet



Skopweb
08-21-2009, 02:17 AM
Hello All
I have a sheet that is password protected, this sheet is distributed to a number of people . Once they fill the information they send it to the reporting person. the sheet is protected in such a way that locked cells cannot be selected and so on .
Is there a way that when this reporting person opens these sheets from his PC they unlock automatically without entering password manually for each sheet so he can select the locked cells. The password is common for all sheets..

Bob Phillips
08-21-2009, 03:19 AM
You could add some code in the workbook open to check the logged in user (Environ("Username")), and if an allowed person unprotect the sheet. Just remember to protect and save before closing.

Skopweb
08-21-2009, 04:34 AM
XLD
i believe this may work ..... but u see i'm not good at creating scripts
can u help me on this please

Bob Phillips
08-21-2009, 04:44 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Worksheets("Sheet1").Protect "password"
Me.Save
End Sub

Private Sub Workbook_Open()
If Environ("Username") = "Skopweb" Then

ThisWorkbook.Worksheets("Sheet1").Unprotect "password"
End If
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Skopweb
08-21-2009, 07:02 AM
Hello XLD
Again u have reached to my rescue
this works perfect..
thanks :bow: :beerchug: :bow: