Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Binding data to a ListBox without using cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Binding data to a ListBox without using cells
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Binding data to a ListBox without using cells
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Binding data to a ListBox without using cells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Binding Pictures to Cells | Excel Discussion (Misc queries) | |||
Late Binding examples of binding excel application | Excel Programming | |||
XML Data Binding | Excel Programming | |||
EARLY binding or LATE binding ? | Excel Programming | |||
Putting Listbox data in cells | Excel Programming |