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