Change event

[pam53154]

I am new to VB programming for change events and have been trying to stumble through... but would appreciate some help. I am trying to write code which says if a user attempts to change cell A1, display a message box. Right now I have this and it is not working. Any thoughts? Thanks!!

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$1" Then
If .Value <> "desired value" Then
MsgBox "Please don't change this cell"
.Select
End If
End If
End With
End Sub

 

[GTO]

Try:

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
    
        If Not Range("A1").Value = "desired value" And Not Range("A1").Value = vbNullString Then
            Range("A1").ClearContents
            MsgBox "Please don't change this cell", 0, ""
            Range("A1").Select
        End If
    End If
End Sub

 

[pam53154]

I think I might be doing something wrong? I copied the code into VB, changed "desired value" to "10", then input several different values in cell A1 and nothing happened. I pulled other sample codes and none are working, either... so am I missing something?

 

[GTO]

Hi Pam,

If you left the quote marks around the 10, ditch them.

Mark

 

[pam53154]

OK... figured it out now! I had the code in a module instead of at the worksheet level. Thanks!

 

[GTO]

I am glad you got it