View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ker_01 Ker_01 is offline
external usenet poster
 
Posts: 100
Default 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