Excel VBA problem
 

Being new to Excel and only just getting around formula's, I would hav
no idea about how to write a simple VBA program. I was told the bes
place to go would be this excellent forum (Hint!!!!)...My problem i
this..

I have a DVD database with three worksheets. First one is calle
Movies, 2nd Customer Info and third customers.

Basically in Movies, I have name of movie and Ratings etc with cus
name. For instance Rating is in G9:G64), Customer name in J9:J64..No
what I want to do is if a customer tries to rent a movie and it is R1
or R15 depending on Age..The vba would check sheet3/customers for thei
age and date of birth and if that DOB is under 18 a message woul
display that person is under age and not allowed to rent movi
parse...I have tried Data Validation but that only brings up messag
when something is inputted into a cell, for instance I tried to creat
a cell which linked to DOB and if it brought back under 18 it woul
show message but Data validation never worked...Hope this message i
not too long...:(

Mar

--
Message posted from http://www.ExcelForum.com

Excel VBA problem
 

Mark it sounds like you could accomplish this via
a vlookup to bring the age of the customer back to the
movies sheet, and the use an if(age<18,"warning","ok to
rent") or something like that.

The vlookup lets you find an entry in the left hand column
of a database and get an entry on that row.

John

-----Original Message-----
Being new to Excel and only just getting around
formula's, I would have
no idea about how to write a simple VBA program. I was
told the best
place to go would be this excellent forum (Hint!!!!)...My
problem is
this..

I have a DVD database with three worksheets. First one
is called
Movies, 2nd Customer Info and third customers.

Basically in Movies, I have name of movie and Ratings etc
with cust
name. For instance Rating is in G9:G64), Customer name in
J9:J64..Now
what I want to do is if a customer tries to rent a movie
and it is R18
or R15 depending on Age..The vba would check
sheet3/customers for their
age and date of birth and if that DOB is under 18 a
message would
display that person is under age and not allowed to rent
movie
parse...I have tried Data Validation but that only brings
up message
when something is inputted into a cell, for instance I
tried to create
a cell which linked to DOB and if it brought back under
18 it would
show message but Data validation never worked...Hope this
message is
not too long...:(

Mark


---
Message posted from http://www.ExcelForum.com/

.

Excel VBA problem
 

You need a VLOOKUP into the movies database to get the movies details and
another into Customer to get DoB, and use a simple IF to check if okay. You
could highlight discrepancies with Data Validation of conditional
formatting.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark1ace1 " wrote in message
...
Being new to Excel and only just getting around formula's, I would have
no idea about how to write a simple VBA program. I was told the best
place to go would be this excellent forum (Hint!!!!)...My problem is
this..

I have a DVD database with three worksheets. First one is called
Movies, 2nd Customer Info and third customers.

Basically in Movies, I have name of movie and Ratings etc with cust
name. For instance Rating is in G9:G64), Customer name in J9:J64..Now
what I want to do is if a customer tries to rent a movie and it is R18
or R15 depending on Age..The vba would check sheet3/customers for their
age and date of birth and if that DOB is under 18 a message would
display that person is under age and not allowed to rent movie
parse...I have tried Data Validation but that only brings up message
when something is inputted into a cell, for instance I tried to create
a cell which linked to DOB and if it brought back under 18 it would
show message but Data validation never worked...Hope this message is
not too long...:(

Mark


---
Message posted from http://www.ExcelForum.com/

Excel VBA problem
 

I tried that, but the age returns 12/01/00, and not sure how to add th
IF statement...:( I tried to do a VBA but was not sure how to list on
column...doing my head in ...
Mar

--
Message posted from http://www.ExcelForum.com

Excel VBA problem
 

Mark,

Let's assume that the customer data is structured name in column A, DoB in
column B, then the date tests are


=IF(AND(G9="R18",DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))<VLOOKUP(
J9,'Customer'!A1:B100,2,FALSE),"Ok","Reject")

and


=IF(AND(G9="R15",DATE(YEAR(TODAY())-15,MONTH(TODAY()),DAY(TODAY()))<VLOOKUP(
J9,'Customer'!A1:B100,2,FALSE),"Ok","Reject")

Best way would be to have coilumns with intermeditae resluts and then test
these on the master sheet.

If you want to send me the workbook, I'll take a look for you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mark1ace1 " wrote in message
...
I tried that, but the age returns 12/01/00, and not sure how to add the
IF statement...:( I tried to do a VBA but was not sure how to list one
column...doing my head in ...
Mark


---
Message posted from http://www.ExcelForum.com/

Excel VBA problem
 

I would love to email you, so that you can take a look..I was going t
try a message box, but ain't great with VB :(

Mar

--
Message posted from http://www.ExcelForum.com

Excel VBA problem
 

Tried that formula no go...Now could it be as my dates are in Englis
date system and not the American one?

Mar

--
Message posted from http://www.ExcelForum.com

Excel VBA problem
 

Nope tried changing to American format and still not working :

--
Message posted from http://www.ExcelForum.com