Data validation using calculated list
 

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

Data validation using calculated list
 

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

Data validation using calculated list
 

Your question was originally:
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.

So I showed you how to do that. Building that string to meet your criteria
should be pretty straightforward

Sub Tester2()
Dim sStr As String
Dim i As Long
sStr = ""
For i = Year(Date) - 1 To Year(Date) + 1
sStr = sStr & i & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
Range("A1").Select
Range("A1").Validation.Delete
Range("A1").Validation.Add _
xlValidateList, , , sStr

End Sub


Worked like a champ for me in xl2000

--
Regards,
Tom Ogilvy




"Max" wrote in message
...
Sorry, I don't think I made myself clear. I want the
routine to calculate the list of 3 years based on the
current system date [Yr1=Year(Date))-1, etc.] and then
insert the list of 3 years into the ".Validate"
statement. This needs to occur at run time in order to
make the list update with each advance of the system date
(current year). Getting the CALCULATED list of years into
the ".Validate" statement is where I'm having problems.

Max

-----Original Message-----
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


.

Data validation using calculated list
 

Tom -

Yes, your suggestion to build the text string worked. And
so easy to implement! Thanks! I was stuck on trying to
use an array of numeric values to enter the 3 years for
the list.

Thanks again for your help.

Max

-----Original Message-----
Your question was originally:
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.

So I showed you how to do that. Building that string to
meet your criteria
should be pretty straightforward

Sub Tester2()
Dim sStr As String
Dim i As Long
sStr = ""
For i = Year(Date) - 1 To Year(Date) + 1
sStr = sStr & i & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
Range("A1").Select
Range("A1").Validation.Delete
Range("A1").Validation.Add _
xlValidateList, , , sStr

End Sub


Worked like a champ for me in xl2000

--
Regards,
Tom Ogilvy




"Max" wrote in message
...
Sorry, I don't think I made myself clear. I want the
routine to calculate the list of 3 years based on the
current system date [Yr1=Year(Date))-1, etc.] and then
insert the list of 3 years into the ".Validate"
statement. This needs to occur at run time in order to
make the list update with each advance of the system
date
(current year). Getting the CALCULATED list of years
into
the ".Validate" statement is where I'm having problems.

Max

-----Original Message-----
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


.



.