PDA

View Full Version : Solved: Populating a Listbox from multiple name ranges (Excel '97)



phendrena
06-23-2009, 05:01 AM
Hello all,

I've currently got a list of staff setup that are sectioned alphabetically. They are setup in sectioned named ranges across the columns rather than in one long list. This has been done to make updating the list easier.

Now, I also have a userform setup whereby people can choose a name. Currently the user needs to pick a letter, which will then display the names starting with that letter.

What I would like to do is to have the listbox default to show ALL the names in one list.

Would anyone be able to suggest how to do this in VBA?

Thanks,

p45cal
06-23-2009, 03:18 PM
this may do what you need:Private Sub UserForm_Initialize()
'"French" and "English" are named ranges
For Each rng In Array(Range("French"), Range("English"))
For Each cll In rng
ComboBox1.AddItem cll.Value
Next cll
Next rng
End Sub

phendrena
06-24-2009, 12:26 AM
this may do what you need:Private Sub UserForm_Initialize()
'"French" and "English" are named ranges
For Each rng In Array(Range("French"), Range("English"))
For Each cll In rng
ComboBox1.AddItem cll.Value
Next cll
Next rng
End Sub

Thank you. That does help.
However, the problem is that my Named Ranges are 5 columns wide. The above code assumes that the Range is just a single column.
Would you be able to suggest how to adjust this code to take into account the 5 column width of the named ranges.

Thanks,

Aussiebear
06-24-2009, 02:47 AM
Check the columns property within the Combobox to see if you can set it a multicolumn

p45cal
06-24-2009, 03:01 AM
re:"The above code assumes that the Range is just a single column"
Not quite, it takes each cell in turn in all the named ranges cited, regardless of the arrangement of cells within the range - it will even handle non-contiguous named ranges. So are you saying that not all cells in the named ranges contain names that you want to appear, and/or as Aussiebear suggests, you want more columns in your combobox?

phendrena
06-24-2009, 03:27 AM
I think we getting a little confused, i'm using a Listbox not a Combobox.
I do want everything in the name range to appear, but i need it to list each 5 column row on one line.

p45cal
06-24-2009, 04:56 AM
I'm glad you softened the tone of your last message; it's so easy for those trying to help you to stop.

That said, try:
Private Sub UserForm_Initialize()
'"French" and "English" are named ranges, each 5 columns wide
For Each rng In Array(Range("French"), Range("English"))
' ListBox1.List() = rng.Value
For Each rw In rng.Rows
ListBox1.AddItem
For ColNo = 0 To 4
ListBox1.List(ListBox1.ListCount - 1, ColNo) = rw.Cells(ColNo + 1).Value
Next ColNo
Next rw
Next rng
End Sub

phendrena
06-24-2009, 05:21 AM
I'm glad you softened the tone of your last message; it's so easy for those trying to help you to stop.

That said, try:
Private Sub UserForm_Initialize()
'"French" and "English" are named ranges, each 5 columns wide
For Each rng In Array(Range("French"), Range("English"))
' ListBox1.List() = rng.Value
For Each rw In rng.Rows
ListBox1.AddItem
For ColNo = 0 To 4
ListBox1.List(ListBox1.ListCount - 1, ColNo) = rw.Cells(ColNo + 1).Value
Next ColNo
Next rw
Next rng
End Sub
Thanks for the amended code.
That works rather nicely.

Yes, i would also like to apologise for the tone of the original reply. Highly unlike myself as i'm sure you can see from other posts.

cheers

:beerchug:

Jayb0ne
09-01-2014, 05:47 AM
Appreciate this is a fairly old thread, but just wanted to pop in to say this method solved my problem really well! I was passing the range to an array and then using the array to populate the listbox, but a discontiguous array would only recognise the first range in the array (the header) and not the second (the actual data). This works great and adds the header at the top with the data from the second range below it.

Nice one! :)

GTO
09-02-2014, 03:31 AM
IMHO, a super-cool thing to mention!

snb
09-02-2014, 04:13 AM
Alternative ?
Assuming the named ranges all start in row 1


Sub M_snb()
With CreateObject("scripting.dictionary")
For Each ar In Rows(1).SpecialCells(2).Areas
For Each rw In ar.CurrentRegion.Rows
.Item("it_" & .Count) = rw
Next
Next

ListBox1.List = Application.Index(.items, 0, 0)

End With
End Sub

NB. The additem method is rather slow to populate a combobox/listbox
With my suggestion the whole row will be added in one go.