Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - Calculated Error Title | Excel Worksheet Functions | |||
Data Validation in Calculated Cells | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data Validation on a calculated cell | Excel Discussion (Misc queries) |