I'm trying to set up a data validation routine in
the "Worksheet.Activiate" module that will calculate the
list (current year, previous year and next year) whenever
the sheet is activated and present the list of three
years as a dropdown in the cell. I can't figure out how
to build the list in a way that I can assign it to the
cell validation WITHOUT FIRST STORING IT IN A RANGE OF
CELLS. I think this could be done with an array, but I'm
not sure just how to accomplish it. Any help?

Example: Range("A1").Validation.Modify xlValidateList,
xlValidAlertStop, ?????????

How can I get my list of 3 years into the statement
without using a range on the sheet? Should I first store
the 3 years in an array? Use a formula(s) as a Defined
Named?

Thanks for any suggestions.

Max

Sub Tester11()

Range("A1").Select
Range("A1").Validation.Delete
Range("A1").Validation.Add _
xlValidateList, , , "2002,2003,2004"

End Sub

Regards,
Tom Ogilvy

Max wrote in message
...
I'm trying to set up a data validation routine in
the "Worksheet.Activiate" module that will calculate the
list (current year, previous year and next year) whenever
the sheet is activated and present the list of three
years as a dropdown in the cell. I can't figure out how
to build the list in a way that I can assign it to the
cell validation WITHOUT FIRST STORING IT IN A RANGE OF
CELLS. I think this could be done with an array, but I'm
not sure just how to accomplish it. Any help?

Example: Range("A1").Validation.Modify xlValidateList,
xlValidAlertStop, ?????????

How can I get my list of 3 years into the statement
without using a range on the sheet? Should I first store
the 3 years in an array? Use a formula(s) as a Defined
Named?

Thanks for any suggestions.

Max