Undoing User Change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'd like to have a Change event macro that will prevent a user from changing cells O12 and in my worksheet. My rationale is that, when a user changes either or these cells, the macro will undo their change, and then display a message box telling them that they can't change the cell. The current code is as below

--------------------
Private Sub Worksheet_Change(ByVal Target As Range

If Target.Address = "$O$12" Or Target.Address = "$H$5" The
Application.Und
MsgBox "Sorry, You are not allowed change this cell!", vbOKOnly, "Permission Denied!
End I

End Su
-------------------

Unfortunately, instead of just 1 message box, a number of message boxes pop up

Can someone please help

Thanks very much in advance

SuperJas
 
SuperJas


I think that the undo action is also triggering the change event.

try

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$O$12" Or Target.Address = "$H$5" Then
application.enableevents = false
Application.Undo
application.enableevents = true
MsgBox "Sorry, You are not allowed change this cell!", vbOKOnly,
"Permission Denied!"
End If

End Sub
 
Back
Top