getting text for unselected listbox items

tgreddy

New Member
Joined
Feb 24, 2006
Messages
49
Hey everyone,

I've been stuck on this problem for quite a few hours and was hoping you could help me. I have two listboxes that contain roughly 5 items each. These items can be moved back and forth between the two listboxes...but when I'm done and click the "OK" button, some validation needs to be done. The validation involves going through each item in each listbox, but I'm running into a problem retrieving the text of each item in the listbox because they aren't selected.

So in short...my question is...how do I retrieve the text of an unselected listbox item if I know the index. Thanks.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

tgreddy

New Member
Joined
Feb 24, 2006
Messages
49
I guess for simplicity purposes...you could assume that I'm trying to dump the listbox items text into Sheet1. So what I want to do is look at the last listbox item and take the text and put it in A1, then I want to look at the 2nd to last item, take the text and put it in A2...etc...until I get to the top of the listbox. I tried the below code, but the bolded portion doesn't work. Thanks for the help in advance!

Range("A1").select
For i = Form1.IncludedSecurityListBox.ListCount - 1 To 0 Step -1 activecell.offset(0,i).value = Form1.IncludedSecurityListBox.Text(i)
Next i
 

tgreddy

New Member
Joined
Feb 24, 2006
Messages
49
anyone have a better suggestion for the bolded, non-functional, code above?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this.
Code:
Dim I As Long
For I = Form1.IncludedSecurityListBox.ListCount - 1 To 0 Step -1
    Range("A1").Offset(, I).Value = Form1.IncludedSecurityListBox.List(I)
Next I
By the way why are you looping backwards?
 

tgreddy

New Member
Joined
Feb 24, 2006
Messages
49
Thanks. I haven't tried the code yet, but to answer your question, I'm looping backwards because my listbox is sorted in ascending order, but the list that is finally output needs to be in reverse order...so rather than going forward and having to re-sort the list...I figured I'd go backwards and save myself a step. Is there a quick way to sort a listbox?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,091
Messages
5,545,906
Members
410,711
Latest member
Josh324
Top