PDA

View Full Version : Popup Message when Specific Text Selected in a Range



craigos
03-24-2013, 02:15 AM
Hi All,

I need help on getting a message popup when specific text is selected from a Validation list in a Column Range.

The Task.

Cells D3 to D300 have Validated list.
On selecting any cell in the range and chosing from the list "Yes - Over 27 Days" a message pops up informing the user what action to take i.e. "You must recalculate the Start Date" with vbOKOnly and on selecting OK the previous cell in Colum C (where the start date is) is selected.

I have tried many variations on the theme using forums but can't find my precise answer.

Many Thanks

Craig

mancubus
03-24-2013, 09:30 AM
hi.

goes to the related worksheet's code module


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3:D300")) Is Nothing Then Exit Sub
If Target.Value = "Yes - Over 27 Days" Then
MsgBox "You must recalculate the Start Date", vbOKOnly, "Over 27 Days"
Target.Offset(-1, -1).Activate
End If
End Sub

craigos
03-24-2013, 10:34 AM
Thanks mancubus,

With a slight modification to this line:
Target.Offset(-1, -1).Activate to
Target.Offset(-0, -1).Activate
Works a treat, many many thanks

Craig

mancubus
03-24-2013, 01:26 PM
you are welcome.

somehow i thought it was previous "row". sorry for that.
as you know we dont need to negate 0's.

so
Target.Offset(0, -1).Activate
or
Target.Offset(, -1).Activate