Data validation using calculated list

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

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
 
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.
Click to expand...

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 said:
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




.
Click to expand...
Click to expand...
 
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.
Click to expand...

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 said:
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

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


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top