How to create a dropdown list of dates with validation?

[Susan Hayes]

Im trying to make a drop down list with dates, but cannot do it

here is the code in vba editor
(I have also tried it with record macro to work backwards)

dim curdate as date
dim day1
dim day2
dim datearray(3) as date

curdate= Date
day1=curdate +1
day2=curdate+2

datearray(1)=curdate
datearray(2)=day1
datearray(3)=day2

with range("e5").validation
.add xlvalidatelist, xlvalidalertstop, xlbetween, datearray
.incelldropdown = true
end with

Thanks
John

 

[Bob Phillips]

Susan,

I don't think you can directly assign an array. You could drop the array
into a worksheet range, or you could try this variation

Dim sDates

sDates = Format(Date, "dd/mm/yyyy") & "," & _
Format(Date + 1, "dd/mm/yyyy") & "," & _
Format(Date + 1, "dd/mm/yyyy")

With Range("e5").Validation
..Delete
..Add xlValidateList, xlValidAlertStop, , sDates
..InCellDropdown = True
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)