Solved: Data validation cell change - message box

[Derek]

I have a cell which has a "yes/no" option using data validation. I want a messagebox to appear when the user selects "yes". I have tried "worksheet_change" but obviously the message keeps appearing when others cells are changed. I only want it to happen when the "yes/no" cell is changed. can anyone help?

Thanks in adavnce

Derek

[Tommy]

In the worksheet_change event check the target.row, target.column to see if that is the correct cell and then change otherwise don't.

[RichardSchollar]

In the worksheet_change event check the target.row, target.column to see if that is the correct cell and then change otherwise don't.

Wouldn't just a check on Target.Address suffice?

[Derek]

Can either of you help me with some sample code for your suggestions?

[RichardSchollar]

Something like:

[VBA]If Not Intersect(Target, Range("YourCellAddress")) Is Nothing Then 'your cell is selected
If Target.Value = "Yes" Then MsgBox "Your Message"
End If[/VBA]

In the worksheet_change event. Won't fire unless it is the specific cell that you reference in Range("YourCellAddress") bit.

Richard

[Tommy]

Wouldn't just a check on Target.Address suffice?

Looks to me they would be the same, six or half a dozen but good point

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
MsgBox "!"
End If
If Target.Address = "$A$1" Then
MsgBox "!!"
End If
End Sub
[/VBA]

[lucas]

For me it works better with the validation if you use worksheet change:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Text = "Yes" Then
MsgBox "!!"
End If
End If
End Sub
[/VBA]

[Derek]

Guys, Thanks to you this code works beautifully. I have also written a worksheet deactivate code to change the cell back to "No" when exiting.

This forum fixes my problems again!

Thank you all.

Derek