Selected Answer
Hi Saxon and welcome to the Forum
In the attached file, Sheet1 is all unlocked except for cell D45 (in yellow). The sheet is protected by the password Master Password but all cells can be selected.
When a cell is selected, it triggers the Worksheet_SelectionChange event which triggers the code below. (Note that I've firstly declared two variables MastPwd and Pwds, before the sub so they have a wider scope).
Private MastPwd As String, Pwds As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lcked As Range
' state the locked cells(s)
Set Lcked = Range("D45")
If Target.CountLarge = 1 And Not Intersect(Target, Lcked) Is Nothing Then
' single cell selected= locked cell(s) so get user password
r = InputBox("Please enter your personal password before editing this cell", "Password required...", "****")
If r <> vbNullString Then
' set up passwords
Call SetPwds
' loop through password array...
For n = LBound(Pwds) To UBound(Pwds)
'unlock sheet if password found
If Pwds(n) = r Then ActiveSheet.Unprotect (MastPwd)
Next n
End If
Else
' not locked cell(s) so protect sheet
ActiveSheet.Protect (MastPwd)
End If
End Sub
The line in bold above calls another sub, also behind sheet1:
Private Sub SetPwds()
' record the sheet password here:
MastPwd = "MasterPassword"
' record individual passwords here:
Pwds = [{"123", "abc", "doreyme", "xyz", "789"}]
End Sub
where you need to change the bits in bold to list your (new) sheet password and your users' passwords (however many- the other code will handle them).
If the user enters a recognised password, D45's value can be changed in Excel's formula bar. If not, the user is prevented from doing so and is told it's a protected cell.
Hope this works for you- if so, kindly mark this answer as Selected.
Happy New Year!
