Password Protect a Cell w/ a Macro

[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

 

[Guest]

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

Vaya con Dios,
Chuck, CABGx3

 

[storminspank]

So what do the Excel gurus on here suggest?

 

[Dave Peterson]

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

 

[storminspank]

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!!!!!!!!

 

[Dave Peterson]

Take a look at this thread:

http://groups.google.co.uk/group/mi...nsubject:copied&rnum=1&hl=en#352b087986130f79

or

http://snipurl.com/l02c