+ Reply to Thread
Results 1 to 4 of 4

Binding data to a ListBox without using cells

  1. #1
    Max
    Guest

    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. #2
    Roman
    Guest

    Re: 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. #3
    Max
    Guest

    Re: 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. #4
    Roman
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1