Referencing Custom Lists?

  • Thread starter Thread starter Jack Schitt
  • Start date Start date
J

Jack Schitt

Is it possible (and if so, how) to create a named array that refers to a
custom list?
Eg in Tools/Options/Custom Lists there is a list of the months "January,
February, March, etc". If I wanted to create a named array that matches
this, can I use the existing custom list for this purpose?

If it cannot be done without a VBA solution but there is a VBA solution I
would be interested in that also.

Thanks.
 
Why not just type January into a cell>use the fill handle to copy down 11
rows to get your list>highlight it> gotot the name box to the left of the
formula box and type in a name of your choice>enter. Voila
 
I think you'd need code:

Take a look at application.getcustomlistcontents in the help and you'll see that
you can acces it directly.

For example:

Option Explicit
Sub testme()
Dim listArray As Variant
Dim iCol As Long
Dim wks As Worksheet

Set wks = Worksheets.Add

For iCol = 1 To Application.CustomListCount
listArray = Application.GetCustomListContents(iCol)
wks.Cells(1, iCol).Resize(UBound(listArray) _
- LBound(listArray) + 1, 1).Value _
= Application.Transpose(listArray)
Next iCol

End Sub

And I think the first 4 custom lists are built into excel and can't be changed.

Specifically, you'd want:
listArray = Application.GetCustomListContents(4)
 
Yes, I realise that would work for the particular example cited.
I am still interested to know if there is a solution to the precise question
asked, rather than a work-around that will I know achieve the same result.

--
Return email address is not as DEEP as it appears
Don Guillett said:
Why not just type January into a cell>use the fill handle to copy down 11
rows to get your list>highlight it> gotot the name box to the left of the
formula box and type in a name of your choice>enter. Voila
Click to expand...
 
there is no way I know of to refer to a custom list, so I don't believe you
can do it directly. With VBA:

Sub Tester2()
Dim varr As Variant
varr = Application.GetCustomListContents(4)
ThisWorkbook.Names.Add Name:="Months", RefersTo:=varr
End Sub
 
Many thanks Tom, gold dust as always

--
Return email address is not as DEEP as it appears
Tom Ogilvy said:
there is no way I know of to refer to a custom list, so I don't believe you
can do it directly. With VBA:

Sub Tester2()
Dim varr As Variant
varr = Application.GetCustomListContents(4)
ThisWorkbook.Names.Add Name:="Months", RefersTo:=varr
End Sub
Click to expand...
 
Back
Top