Binding data to a ListBox without using cells

[Max]

Hi Group,

I want to populate a ListBox with values returned from a query. I want the
user to see one list of text but when the user selects a value from the drop
down an ID is used instead. For example:
1, red
2, orange
3, green

The user sees red, orange and green. When they select orange the VBA behind
the scenes uses 2. Seems simple enough (and it probably is). I did some
searching online and I found one answer that discusses the DataSet object but
that was in the context of .NET, I don't know if I can even use anything
similar in Excel / VBA. I've been looking at this on and off for a couple of
days now and I'm
no closer to an answer. At one point I thought it was possible to populate
the ListBox manually as I populated it with values but that didn't seem to
work. I only want to have a ListBox that shows human readable values whilst
using reference IDs in the background. I want to do this without having to
write the values to a sheet somewhere. There is no reason for the sheet to
be poluted with data that is only used internally. I'm positive there must
be a way to do this but I can't find it. Even if the DataSet approach is the
correct way to do I don't know which module needs to be referenced to get
access to this object.

I know one approach _could_ be to populate an array at the same time as a
the ListBox. The array and the ListBox would use the same indexing so when
the user selects orange which is in position 2 of the ListBox (index=1), I
could then query the array(1) to get the ID that I want. e.g.

ListBox
0, red (index, value)
1, orange
2, green

Array
0,1 (index, ID)
1,2
2, 3

I will do that if I have to but I am hoping there is a cleaner method. Just
think of the manual maintenance should the ListBox values change (and in my
application they do).

Cheers, Max

 

[Roman]

Hi Max
use this to feed the form:

Sub feedtheform()
mylist = Array("red", "orange", "green")
UserForm1.ComboBox1.List = mylist
UserForm1.Show
End Sub

and this to get your value

Private Sub ComboBox1_Change()
dim myvalue as integer
myvalue = UserForm1.ComboBox1.ListIndex + 1
MsgBox myvalue
End Sub

Hope this helps.

 

[Max]

Hi Roman,

Thanks for the reply. That is kinda what I was thinking about but it's not
an exact fit. Imagine for example that I wanted to use a String as the
alternate value as opposed to an Integer e.g.

Windows XP Professional
Windows XP Home Edition
Linux v8
Linux v9
Fedora Core 3
Fedora Core 4

with index values of:
xpp
xph
l8
l9
fc3
fc4

respectively. I know that you can bind ListBoxes to a columns so that one
cell shows up in the ListBox and another is returned as the Selected ListBox
value. My dilemma is achieving the same results but without actually using
any cells i.e. a memory structure. It doesn't look like that is possible
though.

Cheers, Max

 

[Roman]

Sorry for delay, I was not online for long time.
Actually you do not need to feed anything because listindex is a
property of a list and you can read this property to get your integer.
Or I might not understand your problem...
R.

This is from help:
The ListIndex property contains an index of the selected row in a list.
Values of ListIndex range from -1 to one less than the total number
of rows in a list (that is, ListCount - 1). When no rows are
selected, ListIndex returns -1. When the user selects a row in a
ListBox or ComboBox, the system sets the ListIndex value. The