AddIn to create named range

[AGuyThatNeedsHelp]

I currently have many spreadsheets that use the same data validation lists. I would like to create an AddIn that would store the named arrays so they do not have to be placed in every separate workbook.

This is my attempt so far...

'Code contained in AddIn, showing just 1 of many data validation lists

Dim PickName(3) As Variant
PickName(0) = "Tom"
PickName(1) = "Jack"
PickName(2) = "Harry"
PickName(3) = "Joe"

'And then later in AddIn code called from a worksheet_change

Select Case Range("A1").Value
Case "Name"
With Cell.Offset(0, 1).Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, _
Formula1:="=PickName"
.IgnoreBlank = True
.InCellDropdown = True
End With
.....

Error 1004 highlights the .Add statement. I assume I am defining the array incorrectly. Or my methods are invalid.

Thanks

[Salty Sea Dog]

Can you attach the spreadsheet you're working with?

[romperstomper]

If you want a dropdown, then you have to use a range or a delimited string as far as I know. You can't use a function, unfortunately.