Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    LISTBOX Listindex (Excel 2000)

    Hi all,
    I have a listbox in a form that I would like to extract the value from the listbox for cell input, what I get now as an example is when selecting .25 in the listbox the activecell value is 4.

    I have a function call to the populate the listbox,

    Public Function getsnum()as single ()
    dim x() as single
    dim y as integer
    for x =0

    redim Preserve x(y)
    x(y)= .25
    redim Preserve x(y)
    x(y)= .50
    redim Preserve x(y)
    x(y)= .75
    redim Preserve x(y)
    x(y)=1.0
    getsnum = x
    erase x
    End function

    Private sub command1_click
    Activecell.value=listbox1.listindex
    end sub

    Thanks,
    Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LISTBOX Listindex (Excel 2000)

    ListIndex, as its name indicates, is the number of the selected item, starting at 0: 0 if the first item is selected, 1 if the second item is selected etc. It is not the text of the selected item. To get that, use

    ListBox1.List(ListBox1.ListIndex)

    BTW, something must have gone wrong in posting the function, the code doesn't make sense.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Use:
    Listbox1.value

    instead of
    listbox1.listindex

    If you want the value and the listindex.
    Steve

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Thanks Hans,
    You're right it didn't make sense!

    Public Function getsnum()as single ()
    dim x() as single
    dim y as integer
    for x =0

    redim Preserve x(y)
    x(y)= .25
    y=y+1
    redim Preserve x(y)
    x(y)= .50
    y=y+1
    redim Preserve x(y)
    x(y)= .75
    y=y+1
    redim Preserve x(y)
    x(y)=1.0
    getsnum = x
    erase x
    End function

    Does this now make sense, or should I change the getsnum to string?

    Thanks,
    Darryl.

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Thanks Hans,

    I tried to simplify it, but I failed, I tried the For next Loop, but I didn't have what you have, thanks.

    The X=0, I thought was the starting position of the Listbox, index 0 is the first element of the array of the listbox.
    Maybe I am bit messed up with that?

    Thanks,
    Darryl

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Yes, the listindex starts with 0, but you can assign an array to the list even if its index starts with 1 (or 37, for that matter). The resulting list will start with 0.

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: LISTBOX Listindex (Excel 2000)

    Hans
    I must be missing something. I don't understand how 'a' has any value in the statement:
    <hr> Getsnum = a
    <hr>
    TIA
    Regards
    Don

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LISTBOX Listindex (Excel 2000)

    I think it's a typo Don. Should read:
    Getsnum = x

    Alan

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Alan is correct, it was a typo.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Thanks. Now corrected.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Typo corrected by HansV (originally had Getsnum = a in the next to last line of the function. Thanks to Don for noticing it and to Alan for posting the correction.

    It still doesn't make sense to me. The line "for x = 0" is incorrect, for example. Why don't you just use AddItem to populate the list box, or if you prefer to use a function, simplify it:

    Function Getsnum() As Single()
    Dim x(1 To 4) As Single
    Dim y As Integer
    For y = 1 To 4
    x(y) = .25 * y
    Next y
    Getsnum = x
    End Function

  12. #12
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: LISTBOX Listindex (Excel 2000)

    Hans,
    love this function, I just wanted to ask, do I need to use the "erase x"? Or does it matter?

    Thanks,
    Darryl.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LISTBOX Listindex (Excel 2000)

    x is a local variable within the Getsnum function. It will be destroyed as soon as the function finishes, so there is no need to erase it first.

Posting Permissions

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