change data validation

I have data validation (allows values in a list) set in column A (except for
A1:A5). I want to change the validation list dynamically because the values
i'm interested in will change as a query is refreshed. I've tried creating a
string variable that captures the range (set myRange = Range(("AB2"),
Selection.End(xlDown))) and using that to modify the validation:

Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, , crsRange

but i get this error - "Application-defined or object-defined error".

I get the same error if i try to create myRange as a string variable, too.
The range i want to use for the validation list will change each time a
person uses the file, so i need to get this to work.

Any advice?

Thanks

Hi Rachael

One way is to define a named range which is dynamic. Then use that named
range as the list in your data validation.

To enter a dynamic named range: Insert>Name>Define give it a name eg
"myList" and in the RefersTo box enter:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

This will add a named range to column A in sheet2 which will adjust to
the number of rows of data present in that column.

In your data validation you can then use =myList in the source field.

Hope this helps
Rowan

rachael wrote:
> I have data validation (allows values in a list) set in column A (except for
> A1:A5). I want to change the validation list dynamically because the values
> i'm interested in will change as a query is refreshed. I've tried creating a
> string variable that captures the range (set myRange = Range(("AB2"),
> Selection.End(xlDown))) and using that to modify the validation:
>
> Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, , crsRange
>
> but i get this error - "Application-defined or object-defined error".
>
> I get the same error if i try to create myRange as a string variable, too.
> The range i want to use for the validation list will change each time a
> person uses the file, so i need to get this to work.
>
> Any advice?
>
> Thanks
>

Thanks for the suggestion. I'm still having a problem, and i don't know if
it's a problem with my named range or with the code i'm trying to use to
change the validation list.

I created a named range as suggested below:
myList=OFFSET('My Sheet'!$AB$1,0,0,COUNTA('My Sheet'!$AB:$AB)-1)

I then tried to use that range in the validation code:
Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, ,
Range("[My File.xls]![My Sheet]!myList")

When i try to run the code, i get the following error:
Run-time error '1004':
Method 'Range' of object '_Global' failed

What am i doing wrong?
thanks
rachael


"Rowan" wrote:

> Hi Rachael
>
> One way is to define a named range which is dynamic. Then use that named
> range as the list in your data validation.
>
> To enter a dynamic named range: Insert>Name>Define give it a name eg
> "myList" and in the RefersTo box enter:
> =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
>
> This will add a named range to column A in sheet2 which will adjust to
> the number of rows of data present in that column.
>
> In your data validation you can then use =myList in the source field.
>
> Hope this helps
> Rowan
>
> rachael wrote:
> > I have data validation (allows values in a list) set in column A (except for
> > A1:A5). I want to change the validation list dynamically because the values
> > i'm interested in will change as a query is refreshed. I've tried creating a
> > string variable that captures the range (set myRange = Range(("AB2"),
> > Selection.End(xlDown))) and using that to modify the validation:
> >
> > Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, , crsRange
> >
> > but i get this error - "Application-defined or object-defined error".
> >
> > I get the same error if i try to create myRange as a string variable, too.
> > The range i want to use for the validation list will change each time a
> > person uses the file, so i need to get this to work.
> >
> > Any advice?
> >
> > Thanks
> >
>

Ok, i got it to work by entering the formula you suggest into the Data
Validation dialog box. I'd still be interested in knowing what was wrong with
my code, though!

thanks
rachael

"rachael" wrote:

> Thanks for the suggestion. I'm still having a problem, and i don't know if
> it's a problem with my named range or with the code i'm trying to use to
> change the validation list.
>
> I created a named range as suggested below:
> myList=OFFSET('My Sheet'!$AB$1,0,0,COUNTA('My Sheet'!$AB:$AB)-1)
>
> I then tried to use that range in the validation code:
> Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, ,
> Range("[My File.xls]![My Sheet]!myList")
>
> When i try to run the code, i get the following error:
> Run-time error '1004':
> Method 'Range' of object '_Global' failed
>
> What am i doing wrong?
> thanks
> rachael
>
>
> "Rowan" wrote:
>
> > Hi Rachael
> >
> > One way is to define a named range which is dynamic. Then use that named
> > range as the list in your data validation.
> >
> > To enter a dynamic named range: Insert>Name>Define give it a name eg
> > "myList" and in the RefersTo box enter:
> > =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
> >
> > This will add a named range to column A in sheet2 which will adjust to
> > the number of rows of data present in that column.
> >
> > In your data validation you can then use =myList in the source field.
> >
> > Hope this helps
> > Rowan
> >
> > rachael wrote:
> > > I have data validation (allows values in a list) set in column A (except for
> > > A1:A5). I want to change the validation list dynamically because the values
> > > i'm interested in will change as a query is refreshed. I've tried creating a
> > > string variable that captures the range (set myRange = Range(("AB2"),
> > > Selection.End(xlDown))) and using that to modify the validation:
> > >
> > > Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, , crsRange
> > >
> > > but i get this error - "Application-defined or object-defined error".
> > >
> > > I get the same error if i try to create myRange as a string variable, too.
> > > The range i want to use for the validation list will change each time a
> > > person uses the file, so i need to get this to work.
> > >
> > > Any advice?
> > >
> > > Thanks
> > >
> >

Don't refer to the range in the code. Include the string that has the
range name, e.g.:

Range("A:A").Validation.Modify xlValidateList, _
xlValidAlertStop, , "=MyList"


rachael wrote:
> Ok, i got it to work by entering the formula you suggest into the Data
> Validation dialog box. I'd still be interested in knowing what was wrong with
> my code, though!
>
> thanks
> rachael
>
> "rachael" wrote:
>
>
>>Thanks for the suggestion. I'm still having a problem, and i don't know if
>>it's a problem with my named range or with the code i'm trying to use to
>>change the validation list.
>>
>>I created a named range as suggested below:
>>myList=OFFSET('My Sheet'!$AB$1,0,0,COUNTA('My Sheet'!$AB:$AB)-1)
>>
>>I then tried to use that range in the validation code:
>> Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, ,
>>Range("[My File.xls]![My Sheet]!myList")
>>
>>When i try to run the code, i get the following error:
>>Run-time error '1004':
>>Method 'Range' of object '_Global' failed
>>
>>What am i doing wrong?
>>thanks
>>rachael
>>
>>
>>"Rowan" wrote:
>>
>>
>>>Hi Rachael
>>>
>>>One way is to define a named range which is dynamic. Then use that named
>>>range as the list in your data validation.
>>>
>>>To enter a dynamic named range: Insert>Name>Define give it a name eg
>>>"myList" and in the RefersTo box enter:
>>>=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
>>>
>>>This will add a named range to column A in sheet2 which will adjust to
>>>the number of rows of data present in that column.
>>>
>>>In your data validation you can then use =myList in the source field.
>>>
>>>Hope this helps
>>>Rowan
>>>
>>>rachael wrote:
>>>
>>>>I have data validation (allows values in a list) set in column A (except for
>>>>A1:A5). I want to change the validation list dynamically because the values
>>>>i'm interested in will change as a query is refreshed. I've tried creating a
>>>>string variable that captures the range (set myRange = Range(("AB2"),
>>>>Selection.End(xlDown))) and using that to modify the validation:
>>>>
>>>> Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, , crsRange
>>>>
>>>>but i get this error - "Application-defined or object-defined error".
>>>>
>>>>I get the same error if i try to create myRange as a string variable, too.
>>>>The range i want to use for the validation list will change each time a
>>>>person uses the file, so i need to get this to work.
>>>>
>>>>Any advice?
>>>>
>>>>Thanks
>>>>
>>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks - that worked!
rachael

"Debra Dalgleish" wrote:

> Don't refer to the range in the code. Include the string that has the
> range name, e.g.:
>
> Range("A:A").Validation.Modify xlValidateList, _
> xlValidAlertStop, , "=MyList"
>
>
> rachael wrote:
> > Ok, i got it to work by entering the formula you suggest into the Data
> > Validation dialog box. I'd still be interested in knowing what was wrong with
> > my code, though!
> >
> > thanks
> > rachael
> >
> > "rachael" wrote:
> >
> >
> >>Thanks for the suggestion. I'm still having a problem, and i don't know if
> >>it's a problem with my named range or with the code i'm trying to use to
> >>change the validation list.
> >>
> >>I created a named range as suggested below:
> >>myList=OFFSET('My Sheet'!$AB$1,0,0,COUNTA('My Sheet'!$AB:$AB)-1)
> >>
> >>I then tried to use that range in the validation code:
> >> Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, ,
> >>Range("[My File.xls]![My Sheet]!myList")
> >>
> >>When i try to run the code, i get the following error:
> >>Run-time error '1004':
> >>Method 'Range' of object '_Global' failed
> >>
> >>What am i doing wrong?
> >>thanks
> >>rachael
> >>
> >>
> >>"Rowan" wrote:
> >>
> >>
> >>>Hi Rachael
> >>>
> >>>One way is to define a named range which is dynamic. Then use that named
> >>>range as the list in your data validation.
> >>>
> >>>To enter a dynamic named range: Insert>Name>Define give it a name eg
> >>>"myList" and in the RefersTo box enter:
> >>>=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
> >>>
> >>>This will add a named range to column A in sheet2 which will adjust to
> >>>the number of rows of data present in that column.
> >>>
> >>>In your data validation you can then use =myList in the source field.
> >>>
> >>>Hope this helps
> >>>Rowan
> >>>
> >>>rachael wrote:
> >>>
> >>>>I have data validation (allows values in a list) set in column A (except for
> >>>>A1:A5). I want to change the validation list dynamically because the values
> >>>>i'm interested in will change as a query is refreshed. I've tried creating a
> >>>>string variable that captures the range (set myRange = Range(("AB2"),
> >>>>Selection.End(xlDown))) and using that to modify the validation:
> >>>>
> >>>> Range("A:A").Validation.Modify xlValidateList, xlValidAlertStop, , crsRange
> >>>>
> >>>>but i get this error - "Application-defined or object-defined error".
> >>>>
> >>>>I get the same error if i try to create myRange as a string variable, too.
> >>>>The range i want to use for the validation list will change each time a
> >>>>person uses the file, so i need to get this to work.
> >>>>
> >>>>Any advice?
> >>>>
> >>>>Thanks
> >>>>
> >>>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>