I am trying to automate putting validation in a cell that allows selecting from a list of months, starting with the previous month. Using VBA and DateAdd, I can assign the string of months "Jan 2004,Dec 2003,Nov 2003...etc." to the variable AllDates (Dim AllDates as Variant) .

After running the VBA code, I get #VALUE! displayed in the cell when I use the validation dropdown arrow. The code is:

For i = 1 To 36
DateString = CStr(Format(DateAdd("m", -i, Now()), "mmm yyyy")) & ","
AllDates = AllDates & DateString
Next i

'remove the ending comma
AllDates = Mid(AllDates, 1, (Len(AllDates) - 1))

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=AllDates
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

It seems that Formula1 does not work with Validation and variables.

Any solutions?

Thanks.
Jerry