Results 1 to 13 of 13
Thread: LISTBOX Listindex (Excel 2000)

20050427, 12:53 #1
 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.

20050427, 12:58 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050427, 13:13 #3
 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

20050427, 13:58 #4
 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.

20050427, 15:09 #5
 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

20050427, 15:24 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050428, 03:03 #7
 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 = aTIA
<hr>Regards
Don

20050428, 04:49 #8
 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

20050428, 07:05 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: LISTBOX Listindex (Excel 2000)
Alan is correct, it was a typo.

20050428, 07:05 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: LISTBOX Listindex (Excel 2000)
Thanks. Now corrected.

20050428, 07:07 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20050428, 12:31 #12
 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.

20050428, 14:23 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.