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