Use VBA to reset data validation (=list) value to first value in that list (list is a named range)
 

I have a cell that has data validation (=list), populated by a named range

In my VBA, I need to be able to reset that list to the first value in the
list. I had the code working, but I must have tweaked something without
knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith

Use VBA to reset data validation (=list) value to first value inthat list (list is a named range)
 

try taking out the (1)

Sheet12.Range("E2").Value = Range(S)

as far as i can see you don't declare it as a variable anywhere, it
just seems like it just popped in & is doing nothing.
hope that helps
:)
susan



On Oct 20, 12:39*pm, "ker_01" wrote:
I have a cell that has data validation (=list), populated by a named range

In my VBA, I need to be able to reset that list to the first value in the
list. I had the code working, but I must have tweaked something without
knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
* * 'just reset the date back to earliest date in the list
* * S = Sheet12.Range("E2").Validation.Formula1
* * Sheet12.Activate
* * Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
* * Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith

Use VBA to reset data validation (=list) value to first value in that list (list is a named range)
 

That works fine for me.

What error do you get?

--
__________________________________
HTH

Bob

"ker_01" wrote in message
...
I have a cell that has data validation (=list), populated by a named range

In my VBA, I need to be able to reset that list to the first value in the
list. I had the code working, but I must have tweaked something without
knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith

Use VBA to reset data validation (=list) value to first value in that list (list is a named range)
 

Susan-
Thank you for your response. I set S to the validation formula (which is a
named range list), and my goal is to reset the contents of that data
validation cell to the first value in the available list (hence the final
(1)). When I remove it, I get a 1004 runtime error method 'range' of object
'_worksheet' failed, which is the same error message I get with the (1) in
place.

I welcome any other thoughts
:)
Thanks,
Keith


"Susan" wrote in message
...
try taking out the (1)

Sheet12.Range("E2").Value = Range(S)

as far as i can see you don't declare it as a variable anywhere, it
just seems like it just popped in & is doing nothing.
hope that helps
:)
susan



On Oct 20, 12:39 pm, "ker_01" wrote:
I have a cell that has data validation (=list), populated by a named range

In my VBA, I need to be able to reset that list to the first value in the
list. I had the code working, but I must have tweaked something without
knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith

Use VBA to reset data validation (=list) value to first value in that list (list is a named range)
 

I get a 1004 runtime error, method 'range' of object '_worksheet' failed.

I also tried S(1) instead of Range(S)(1) but got a runtime error 13 type
mismatch

Sheet12.Range("E2").Validation.Formula1 = "=DatesForSelection14Days"

The source list is a list of dates, where the first item in that list is
today's date, then tomorrows, then the next day (etc). I have code that
updates that list so that when raw data is loaded, the list of dates is
updated to start with "todays" date. So for example, the first item in the
list today is '10/21/2008'

So, looking at the code I think I just figured something out (maybe the
problem, or maybe just something I don't understand): In the following code,
S evaluates to S = "=DatesForSelection14Days" which is a string, not a
range/array. I don't get any autotext options behind "Formula1" but now I'm
thinking that maybe Range(S)(1) doesn't recognize this as a named range?

Thank you for any additional help,
Keith

If Target.Address = "$B$2" Then 'dept changed
'graph updates are handled with dynamic named ranges
'just reset the date back to earliest date possible
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range (S)(1)
Set Target = Sheet12.Range("$E$2")
End If


"Bob Phillips" wrote in message
...
That works fine for me.

What error do you get?

--
__________________________________
HTH

Bob

"ker_01" wrote in message
...
I have a cell that has data validation (=list), populated by a named range

In my VBA, I need to be able to reset that list to the first value in the
list. I had the code working, but I must have tweaked something without
knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith

Use VBA to reset data validation (=list) value to first value inthat list (list is a named range)
 

keith - i've just spent the last 1/2 hr trying to recreate your
problem.
i can't make it work, myself.
and you have a "1" at the end of .formula. don't know if that's
supposed to be there, because i can't make it work to test it.
sorry!
susan


On Oct 21, 10:27*am, "ker_01" wrote:
Susan-
Thank you for your response. I set S to the validation formula (which is a
named range list), and my goal is to reset the contents of that data
validation cell to the first value in the available list (hence the final
(1)). When I remove it, I get a 1004 runtime error method 'range' of object
'_worksheet' failed, which is the same error message I get with the (1) in
place.

I welcome any other thoughts
:)
Thanks,
Keith

"Susan" wrote in message

...
try taking out the (1)

Sheet12.Range("E2").Value = Range(S)

as far as i can see you don't declare it as a variable anywhere, it
just seems like it just popped in & is doing nothing.
hope that helps
:)
susan

On Oct 20, 12:39 pm, "ker_01" wrote:



I have a cell that has data validation (=list), populated by a named range

In my VBA, I need to be able to reset that list to the first value in the
list. I had the code working, but I must have tweaked something without
knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith- Hide quoted text -

- Show quoted text -

Use VBA to reset data validation (=list) value to first value in that list (list is a named range)
 

Another possible issue (possibly only because of my lack of understanding
about the parameters); on any given day, the list shows today +14 days. When
the macros run, they update the source range for that cell, but the existing
value may be yesterday's date (or earlier) and not a valid value.

I've been assuming that Range(S)(1) will return the first value from the
source list, and that the current selection in that cell (an old date that
no longer meets the validation criteria) cannot be selected.

Still don't have it working, so any additional advice on what to
troubleshoot would be very much appreciated.

Thanks,
Keith

"ker_01" wrote in message
...

I get a 1004 runtime error, method 'range' of object '_worksheet' failed.

I also tried S(1) instead of Range(S)(1) but got a runtime error 13 type
mismatch

Sheet12.Range("E2").Validation.Formula1 = "=DatesForSelection14Days"

The source list is a list of dates, where the first item in that list is
today's date, then tomorrows, then the next day (etc). I have code that
updates that list so that when raw data is loaded, the list of dates is
updated to start with "todays" date. So for example, the first item in the
list today is '10/21/2008'

So, looking at the code I think I just figured something out (maybe the
problem, or maybe just something I don't understand): In the following
code, S evaluates to S = "=DatesForSelection14Days" which is a string, not
a range/array. I don't get any autotext options behind "Formula1" but now
I'm thinking that maybe Range(S)(1) doesn't recognize this as a named
range?

Thank you for any additional help,
Keith

If Target.Address = "$B$2" Then 'dept changed
'graph updates are handled with dynamic named ranges
'just reset the date back to earliest date possible
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range (S)(1)
Set Target = Sheet12.Range("$E$2")
End If


"Bob Phillips" wrote in message
...
That works fine for me.

What error do you get?

--
__________________________________
HTH

Bob

"ker_01" wrote in message
...
I have a cell that has data validation (=list), populated by a named
range

In my VBA, I need to be able to reset that list to the first value in
the list. I had the code working, but I must have tweaked something
without knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith

Use VBA to reset data validation (=list) value to first value in that list (list is a named range)
 

My final solution was to give up on my existing code, and add a
commandbutton to the sheet- I linked the update throught that commandbutton,
and now everything works as desired (because I eliminated the need to re-set
the combobox value to the first value in the named range list)
Thanks,
Keith

"ker_01" wrote in message
...
Another possible issue (possibly only because of my lack of understanding
about the parameters); on any given day, the list shows today +14 days.
When the macros run, they update the source range for that cell, but the
existing value may be yesterday's date (or earlier) and not a valid value.

I've been assuming that Range(S)(1) will return the first value from the
source list, and that the current selection in that cell (an old date that
no longer meets the validation criteria) cannot be selected.

Still don't have it working, so any additional advice on what to
troubleshoot would be very much appreciated.

Thanks,
Keith

"ker_01" wrote in message
...

I get a 1004 runtime error, method 'range' of object '_worksheet' failed.

I also tried S(1) instead of Range(S)(1) but got a runtime error 13 type
mismatch

Sheet12.Range("E2").Validation.Formula1 = "=DatesForSelection14Days"

The source list is a list of dates, where the first item in that list is
today's date, then tomorrows, then the next day (etc). I have code that
updates that list so that when raw data is loaded, the list of dates is
updated to start with "todays" date. So for example, the first item in
the list today is '10/21/2008'

So, looking at the code I think I just figured something out (maybe the
problem, or maybe just something I don't understand): In the following
code, S evaluates to S = "=DatesForSelection14Days" which is a string,
not a range/array. I don't get any autotext options behind "Formula1"
but now I'm thinking that maybe Range(S)(1) doesn't recognize this as a
named range?

Thank you for any additional help,
Keith

If Target.Address = "$B$2" Then 'dept changed
'graph updates are handled with dynamic named ranges
'just reset the date back to earliest date possible
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range (S)(1)
Set Target = Sheet12.Range("$E$2")
End If


"Bob Phillips" wrote in message
...
That works fine for me.

What error do you get?

--
__________________________________
HTH

Bob

"ker_01" wrote in message
...
I have a cell that has data validation (=list), populated by a named
range

In my VBA, I need to be able to reset that list to the first value in
the list. I had the code working, but I must have tweaked something
without knowing it, and now it doesn't work anymore.

If Target.Address = "$B$2" Then
'just reset the date back to earliest date in the list
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Activate
Sheet12.Range("E2").Value = Range(S)(1) '<- this is the line that
doesn't work
Set Target = Sheet12.Range("E2")
End If

Thanks for any advice,
Keith