MsgBox when value change

[van0912]

Hi all,

I would like to trigger a msgbox when a cell in a worksheet range change from "OK" or "N/A" to "NOK". I have the following code but it is not working (message error is "Object required")... Could you please help me?

Sub Msgbox_statutchange() Dim R As Range Set R = Sheets("List").Range("R4:R500") If Intersect(Target, R) Is Nothing Then Exit Sub If Target.Value = "NOK" Then MsgBox "Statut has changed to " & Target.Address End If End Sub

Thank you!!

 

[RobP]

Hi,

your code is failing as you do not seem to be defining "Target" anywhere ?

where should you get Target from, and what should it be ? Should you be passing it into this subroutine as a parameter ?

Hope that helps
Rob

 

[van0912]

Hi,

your code is failing as you do not seem to be defining "Target" anywhere ?

where should you get Target from, and what should it be ? Should you be passing it into this subroutine as a parameter ?

Hope that helps
Rob

Hello Rob,

Thanks for replying. I'm not sure to understand as I'm just starting on VBA.
I would like to have a msgbox when value in Range R (rows 4 to 500) changes from "OK" or "N/A" to "NOK" (range R corresponds to "status").

Thank you for your help,
Vanessa

 

[Joe4]

The body of your code looks good, but it needs to be contained within a "Worksheet_Change" event procedure, which is VBA code that runs automatically when some cell is changed on the sheet (the code MUST also be put in the "Sheet" module of the sheet you would like it to run against, and not in a General Module.

So the code should look like:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range

Set R = Sheets("List").Range("R4:R500")

If Intersect(Target, R) Is Nothing Then Exit Sub

If Target.Value = "NOK" Then
    MsgBox "Status has changed to " & Target.Address
End If

End Sub

If it does not run automatically, then you have probably not put the code in the correct sheet module (the "List" sheet module).

 

[van0912]

The body of your code looks good, but it needs to be contained within a "Worksheet_Change" event procedure, which is VBA code that runs automatically when some cell is changed on the sheet (the code MUST also be put in the "Sheet" module of the sheet you would like it to run against, and not in a General Module.

So the code should look like:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range

Set R = Sheets("List").Range("R4:R500")

If Intersect(Target, R) Is Nothing Then Exit Sub

If Target.Value = "NOK" Then
    MsgBox "Status has changed to " & Target.Address
End If

End Sub

If it does not run automatically, then you have probably not put the code in the correct sheet module (the "List" sheet module).

Thank you Joe, it works in a "Sheet" module