PDA

View Full Version : Solved: Data validation cell change - message box



Derek
01-17-2007, 01:16 PM
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
01-17-2007, 01:37 PM
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
01-17-2007, 01:48 PM
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
01-17-2007, 02:02 PM
Can either of you help me with some sample code for your suggestions?

RichardSchollar
01-17-2007, 02:05 PM
Something like:

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

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

Richard

Tommy
01-17-2007, 02:08 PM
Wouldn't just a check on Target.Address suffice?

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

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

lucas
01-17-2007, 10:18 PM
For me it works better with the validation if you use worksheet change:
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

Derek
01-18-2007, 02:06 AM
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:beerchug: