MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

About Listbox

  Asked By: Jimmy    Date: Jan 14    Category: MS Office    Views: 838

I have a quick question about populating a listbox object on a
userform in Excel. I am very used to program Listbox in Access form
and I can see a few differences with Userforms.

I want to populate a Listbox with, say, City names and a numeric
value. Let's say that this number is some kind of index. The list
sould show the City names but NOT the associated index value.

Now when I select a city from the Listbox and click the Command
I need to read the City's index and not its name. So:

The list box data source is:
3 Melbourne
7 Sydney
13 Adelaide
23 Brisbane

The listbox shows

If I click Adelaide and click whatever Command button I need to read
from the list box a property that will return 13.

I have worked out how to populate the list box with an array. So I
lstListboxObject.List = aryCityArray()

This populates the Listbox with the names only. I haven't figured
out how to populate with two values per row.

I could also have used:
For i = 1 to <Top Value>
lstListBox.AddItem strCityName
Next i

Even there I tried a few possibilities to find the format for two
values per row. It seems that I need to use a semicolon but I cannot
hide the first column. For example I tried:

For i = 1 to <Top Value>
lstListBox.AddItem intIndex & ";" & strCityName
Next i

Suppose that I set the ColumnCount to 2 the the BoundColumn to 1 or
2. How can I read the bound value for the selected row?

I realise that there a really two different issues here. But they
are related right?



3 Answers Found

Answer #1    Answered By: Martha Gonzalez     Answered On: Jan 14

In essence you put the number  first and have a list  box with 2 columns and set
the first column  width to 0

Answer #2    Answered By: Poppy Brown     Answered On: Jan 14

For populating two or more columns I generally use a two stage thing.
First AddItem to set  up the row  and then Add stuff to each column.

An actual example....

Me.lstStringSetWidth.List(Me.lstStringSetWidth.ListCount - 1, 0) = "Inches"
Me.lstStringSetWidth.List(Me.lstStringSetWidth.ListCount - 1, 1) = "/1000"

You can see that column  reference change from (n, 0) to (n, 1).
Using ListCount means I reference the last line... Which I've just added.

Answer #3    Answered By: Juanita Mason     Answered On: Jan 14

I practice using this dummy function and it populated my Listbox
with the values  1 to 26 and A to Z. Using the tip from David about
hiding the first column  I was able to see only A to Z but read
the Value property  and extract the number  of the letter.

Thanks to all.

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 0 To 25
lstArrayList.List(i, 0) = i + 1
lstArrayList.List(i, 1) = "Column " & Chr(i + 65)
Next i
End Sub

Didn't find what you were looking for? Find more on About Listbox Or get search suggestion and latest updates.