On Saturday, May 24, 2014 7:25:03 PM UTC-7, GS wrote:
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...



<snip

Formula1:=Join(MyArray, ",")



..so this works...



Sub InsertDV()

With Selection.Validation

.Delete

.Add Type:=xlValidateList, _

AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, _

Formula1:=Join(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), ",")

.IgnoreBlank = True: .InCellDropdown = True

.ShowInput = True: .ShowError = True

End With

End Sub





I wasn't seeing the connection between the array being assigned and

your imported data (assuming there was a connection). I also was

curious about the data source (external file) and how it was being

retrieved, what 'properties' you're setting, etc.!



--

Garry



Hi Garry,

That works great. Thanks.

My first thought to using code to produce a DV drop down was the drop down list would surely be data somewhere on the sheet. Just need a way to get it into the 'source window'. I was thinking why would you want to hard code your source list and have to re-enter it into the code each time you wanted a DV.

Still kinda wondering why.

I don't know if I will ever figure out or understand that last paragraph relating to column S where any row has data.

Thanks again.
Howard