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

[ker_01]

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

 

[Susan]

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

 

[Bob Phillips]

That works fine for me.

What error do you get?

 

[ker_01]

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


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

 

[ker_01]

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

 

[Susan]

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

 

[ker_01]

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]

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