Hi Garry,

Am Sat, 24 May 2014 22:25:03 -0400 schrieb GS:

Ok, Claus shows how to assign DV with VBA and so addresses that Q.
What's probably throwing the error is the assignment of the array. The
DV formula needs to be a range or a delimited list. This can be done
using the Join() function...

I wanted to show Howard how to work with this array.
If I have a range I don't read it in an array for DV. I give this range
a name and use the name as source for DV or use the range directly:
Sub DV1()

With Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$F$1:$F$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

or:
Sub DV2()

ActiveWorkbook.Names.Add Name:="MyList", _
RefersTo:="=Sheet1!F1:F10"

With Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=MyList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional