Message box

[Mark1ace1]

I have a column in G which is for ratings such as R 18 and R 15

In column J is customer names, what I want is to bring up a message t
check ID if that customer below tries to rent a R 18 or R 15 movie..
have tried the following without much luck

Private Sub Worksheet_Change(ByVal Target As Range)

If [j9] = "Thomas Chappell" Then MsgBox "Please check ID befor
renting!"

If [j9] = "Robyn Smith" Then MsgBox "Please check ID befor
renting!"

If [j9] = "Xendan Smith" Then MsgBox "Please check ID befor
renting!"
End sub

this is just basic, but I can seem to check the whole column..Or chec
if they are trying to rent a R 18

Mark

PS..In work sheet 3 I have their date of birth, is it possible to chec
date of birth and if < R 18 or R 15 the message box appears, advisin
themt to make another choice..?? Please help I am getting s
frustrated.

 

[Dave Peterson]

I don't think I'd use code for this.

It would be kind of a pain to adjust the code each time a new name/age were
added.

I think I'd create a new worksheet (sheet2?) and then put the names in column A
and the ages in column B.

Then put this in an adjacent cell (column K?)

=IF(J2="","",IF(ISERROR(VLOOKUP(J2,Sheet2!A:B,2,FALSE)),"missing",
VLOOKUP(J2,Sheet2!A:B,2,FALSE)))

Format this column as a date and it'll return the birthdate for each matched
name.

And if you really want, you could use that date in another helper column (column
L?):

=DATEDIF(K2,NOW(),"y") & " years, " & DATEDIF(K2,NOW(),"ym") & " months, "
& DATEDIF(K2,NOW(),"md") & " days"

This =datedif() formula is only documented in xl2k. If you want to see a nice
description, take a look at Chip Pearson's site:

http://www.cpearson.com/excel/datedif.htm

(That's where I stole the second formula!)

I have a column in G which is for ratings such as R 18 and R 15

In column J is customer names, what I want is to bring up a message to
check ID if that customer below tries to rent a R 18 or R 15 movie..I
have tried the following without much luck

Private Sub Worksheet_Change(ByVal Target As Range)

If [j9] = "Thomas Chappell" Then MsgBox "Please check ID before
renting!"

If [j9] = "Robyn Smith" Then MsgBox "Please check ID before
renting!"

If [j9] = "Xendan Smith" Then MsgBox "Please check ID before
renting!"
End sub

this is just basic, but I can seem to check the whole column..Or check
if they are trying to rent a R 18

Mark

PS..In work sheet 3 I have their date of birth, is it possible to check
date of birth and if < R 18 or R 15 the message box appears, advising
themt to make another choice..?? Please help I am getting so
frustrated..