make a named range with an array not using a workseet cell range

[John S.]

I know I can just make a list in a worksheet and name it but I would like to know if there is a way to just manually
input an array into the named range. I tried this but it does not work. I see that the Refers to is correct but the values
is not set. Ultimately this will be used for a dropdown list. Thanks for any help.


Dim MyWeek

MyWeek = Array("","Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

Names.Add Name:="Typelist", RefersTo:=MyWeek

 

[GS]

It happens that John S. formulated :

I know I can just make a list in a worksheet and name it but I would like to
know if there is a way to just manually input an array into the named range.
I tried this but it does not work. I see that the Refers to is correct but
the values is not set. Ultimately this will be used for a dropdown list.
Thanks for any help.


Dim MyWeek

MyWeek = Array("","Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

Names.Add Name:="Typelist", RefersTo:=MyWeek

Validation lists must be a delimited list OR a reference to a range
that contains a list. If you want to add Validation for a specific list
to any cell then this might be the way to go...

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="Mon,Tue,Wed,Thu,Fri,Sat,Sun"
.IgnoreBlank = True: .InCellDropdown = True
.ShowInput = True: .ShowError = True
End With