Password Protect a Cell w/ a Macro

  • Thread starter Thread starter storminspank
  • Start date Start date
S

storminspank

I have a cell on one of worksheets that I need to password protect.
I want the user to be prompt to enter a password once they try to
modify the cell.

What is the easiest way to accomplish this? I am told that using a
macro is the best way to achieve it.


I found this code on here:

Private Sub Worksheet_Change(ByVal Target As Range)
'******unlock all cells in the range first*********
Dim MyRange As Range
Const Password = "tcmvsales99" '**Change password here**
Set MyRange = Intersect(Range("B4"), Target) '**change range
here**
If Not MyRange Is Nothing Then
Unprotect Password:=Password
MyRange.Locked = True
Protect Password:=Password
End If
End Sub

which will protect the cell once data is enter.


Any help would be much appreciated.

Thanks,
Justin
 
Don't forget.........a user can always open the workbook and disable the
macros.

Vaya con Dios,
Chuck, CABGx3
 
Maybe you can just use a worksheet_change event to look for a change to that
cell (I used A1).

Then you don't have to protect the sheet at all. But this will fail if
macros/events are disabled.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim InPWD As String
Const CellPWD = "tcmvsales99"

Set myCell = Me.Range("a1")

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

InPWD = InputBox(prompt:="Please enter password to change: " _
& myCell.Address(0, 0))

If InPWD = CellPWD Then
'ok, keep the change
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox Target.Address(0, 0) & " Changed changed back--wrong password"
End If

errHandler:
Application.EnableEvents = True

End Sub
 
Dave -
Absolutely gorgeous! With a little tweaking, this worked EXACTLY as I
would like it to.
If I can find a way to force my users to have to enable macros, I am
set...

A thousand thanks!

THANK YOU!!!!!!!!
 
Back
Top