Disable message box only appears when user changes an existing value
I have vba code that appears in a field msg
if the entered value is zero. if the user later wants to change the zero to some other number, it will still pop up in the field msg
before he can edit the zero to some other number.
I don't want to msgbox
pop up if the user changes an existing value.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("b4:af18")) Is Nothing Then
If Target.Value = 0 Then
Application.EnableEvents = True
MsgBox "This is it", vbApplicationModal, "Scikess/Holiday"
End If
End If
Application.EnableEvents = True
End Sub
source to share
I believe you are describing a situation where the user enters a delete key before entering a new answer. In this case, a blank cell has a numeric value of zero, and you get a false positive check to see if the user is entered at zero.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b4:af18")) Is Nothing Then
On Error GoTo bm_Safe_Exit
Dim trgt As Range
Application.EnableEvents = False
For Each trgt In Intersect(Target, Range("b4:af18"))
If trgt.Value = 0 And trgt.Text = "0" Then
MsgBox "This is it" & Chr(10) & trgt.Address(0, 0) & " cannot be zero.", _
vbApplicationModal, "Scikess/Holiday"
Application.Undo
trgt.Activate
Exit For
End If
Next trgt
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
I tried to run the code when there is more than one target involved (like an insert or fill operation), but that makes it difficult to keep Application.Undo
other valid entries. Please comment on this behavior if it is unusable.
It was also not visible in what range you want this to react. You seem to have missed Not
in your Intersect method to make it respond to any cell that was not in the range. I configured the above to include the range you specified, not exclude it.
source to share