Message box VBA - trigger to remain constant, need VBA to stop thereafter

Hey all

I have the following code which works upto as required. However when the user selects 'ok' i need the message box to discontinue.

The target cell will remain the same value. i.e. just a warning with no action required.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If [A1].Value < 0 Then
MsgBox "Message here" & vbNewLine & vbNewLine & "Additional Message here", vbExclamation
End If
End Sub

Any help would be appreciated.

Hi Hudibyk

Thanks for the quick response... i see how your code is working, but still can figure out what i need to put following "vbOk" Then.

The code / message box function appears to be looping because [A1] remains constant.

I feel this is a simple fix and am annoyed as i cant figure it out, any additional help would be appreciated.

Thanks

Probbably now I understand, what you want. Try this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ans As String
If [A1].Value < 0 Then
    Application.EnableEvents = False
    ans = MsgBox("Message here" & vbNewLine & vbNewLine & "Additional Message here", vbExclamation + vbOKCancel)
    If ans = vbCancel Then Application.Undo
End If
Application.EnableEvents = True
End Sub

Additionaly read about Buttons argument in MsgBox function, especially vbDefaultButton. It may be useful for you

You sir are a legend!!!!

Just had to fix the reference in the If ans.... and that was it.

Thanks for your help.