protected cell

[kamran89]

I am making an excel sheet, I want to ensure that no one can change data once the data is entered. for that I used following code

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect ("newpass4")
Target.Locked = True
ActiveSheet.Protect ("newpass4")
End Sub




however I want that if someone (administrator/ owner) wants to change a data in cell, the password is requested and on correct password the data can be changed. and this is valid for the cell selected only. Thanks in advance

 

[Musa15]

Why not protect the sheet with a password? Not using VBA?

 

[kamran89]

Data needs to be entered in a routine by different people. I don't want them to change
Made by other people

 

[dave3009]

Hi kamran89, welcome to the board.

You could check the users name and if the user has the required authority to change the workbook

If Environ("username") = "Admin" Then
    ...code to run
End If

Perhaps use the open event to set it in a state editable if the user holds the right permission, and lock again on close.

If you need any further information on how this could work please let me know.

Regards

Dave

 

[kamran89]

excel 2013 with win 10

 

[dave3009]

Hi kamran89

Couple of solutions. First and most obvious is to give the admin/owners the password, that might leak out though...that said if you are looking for security then Excel is not the best choice.

Next is to create a list of system logins for allowed users, use the Windows user name for this. Put the list in a sheet when the workbook opens test if the users name is listed, if it is unlock the sheets, if not then don't. Then on closing the workbook lock the sheets so the next person to open the file can be assessed if they have the authority to access the WB.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> Worksheets<br>    sht.Protect "newpass4"<br><SPAN style="color:#00007F">Next</SPAN> sht<br>ThisWorkbook.Save<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> adm <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Set</SPAN> adm = Sheet3.Range("A1:A20")<br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsError(Application.Match(Environ("username"), adm, 0)) <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        sht.Unprotect "newpass4"<br>    <SPAN style="color:#00007F">Next</SPAN> sht<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You could apply similar methodology to a login system, though it may be a bit of a pest to force a login each time an admin/owner attempts to log in.

HTH, will be around if you want to expand on the solution.

Dave