Start Free Trial

asked on

ListBox filter

I have a list box filled as follows:
Private Sub Form_Load()
Dim i As Integer
Dim str_Sorted As String
Dim J As Integer

frmMain.StatusBar1.Panels.Item(3).Text = "Rolodex"

NameArray(0) = "Lorenzo Bridgewater"
NameArray(1) = "Christian Chavez"
NameArray(2) = "Adan Lozano Jr."
NameArray(3) = "William Mackinnon Jr."
NameArray(4) = "Jaime Madruga"
NameArray(5) = "Barbara Marquart"
NameArray(6) = "Janette Martin"
NameArray(7) = "Paul Martin"
NameArray(8) = "Daphney Mathieu"
NameArray(9) = "William McConville Jr."
NameArray(10) = "James McKay"
NameArray(11) = "Scott McKee"
NameArray(12) = "Alexander McKinstry"
NameArray(13) = "Wayne Mercer"
NameArray(14) = "Allen Morrison"
NameArray(15) = "Richard Newhart"
NameArray(16) = "Hanh Nguyen"
NameArray(17) = "Samantha Nicholas"
NameArray(18) = "Erik Nicotra"
NameArray(19) = "James Norwood"
NameArray(20) = "James Oswald"
NameArray(21) = "Jay Patel"
NameArray(22) = "Kenneth Pokryska"
NameArray(23) = "Nellie Price"
NameArray(24) = "Walter Rodriguez"
NameArray(25) = "Arlene Rose"
NameArray(26) = "Aaron Silverman"
NameArray(27) = "Dennis Spears"
NameArray(28) = "Scott Stadel"
NameArray(29) = "Joseph Turano Jr."
NameArray(30) = "Raul Velez"
NameArray(31) = "Jorge Viloria"
NameArray(32) = "Deborah White"
NameArray(33) = "Hollie White"
NameArray(34) = "Shawna Williams"
NameArray(35) = "Richard Witt Jr."
NameArray(36) = "Christopher Wright"
NameArray(37) = "Sohee Yun"
NameArray(38) = "Scott Zimany"
For i = 0 To UBound(NameArray)
str_Sorted = str_Sorted & NameArray(i) & vbCrLf
lstNames.AddItem (NameArray(i))

Next i

Now I need to press a cmdA button and have it sort the "A"'s and put the number of "A"s in a labelbox (saying something like "You have 3 A entries" and scroll the text box to the first of "A"s. I also need to have 2 seperate command buttons I need to scroll through the list box, one up and one down. I am new to programming and really need help.

Don't quite know where to start with this.

First of all, ListBox has a Sorted property, so if that is set to true, the items are in order.

To count the any first letter

letter = "A" 'for example
i = 0

do until left$(List1.list(i),1) = letter
i = i + 1
loop

List1.Selected(i)= True ' set to first

do until left$(List1.list(i),1) <> letter
letterCount = letterCount+1
i = i + 1
loop

You'll have to put in a test for end of list (ListCount property)

A listbox will display and up and down scroll buttons if necessary, so you don't need command buttons.

ASKER

So on cmdA I would insert that code? or would it be included in the lstBox? Would not the value of i need to be kept in a variable to be output to label? and would I need a seperate code to make the listbox go to the value input? Like if I type B than the list box goes to the first B and in the label it says "You have 4 B entries".

ASKER

I don't think I said that I also have a cmdarray with the letters of the alphabet on the same form with the list box. So the names show in the list box, and when you hit the z button it should go to the x entries in the list box and also display in the label "You have 4 entries in the Xs".

Are you trying to sort these by the first letter of the name as you listed it(first name), or first letter of the last name??

ASKER

I would like to sort by the last name.

Are you trying to sort these by the first letter of the name as you listed it(first name), or first letter of the last name??

ASKER

first letter of the last name.

Try this.....

I made a command button and attached this code behind the button. List1 is the name of my list box, change it to lstNames to fit your program.

Logic:
- goes through each name one-by-one
- splits the name at the space between first and last name, which makes two seperate variables (First and Last)
- reads the first letter of the last name and compares it to letter of choice, in this case M
- if it is M, then it adds 1 to the counter. If it is the first instance of the M it highlights that name in the list
- shows message box with final count of M's.

Copy and paste this behind a command button, change the name, you're good to go.

Private Sub Command2_Click()
Dim selectname As String
Dim indexpos As Integer
Dim strTest As String
Dim strArray() As String
Dim intCount As Integer
Dim First As String
Dim Last As String
Dim a As Integer
Dim CountList As Integer
Dim FirstinList As Integer

FirstinList = 0

CountList = List1.ListCount - 1
For a = 0 To CountList

selectname = List1.List(a)
strArray = Split(selectname, " ")

Last = Trim(strArray(1))
First = Trim(strArray(0))

If Left$(Last, 1) = "M" Then
intCount = intCount + 1
If FirstinList = 0 Then
List1.Selected(a) = True
FirstinList = 1
End If

End If

Next a

MsgBox intCount

End Sub

ASKER

I'm so excited, this is the closest I have been. Now I am getting a run-time eror 9, subscribt out of range. I am a dead new beginner at this and this whole thing seems a set up for failure.

Where does it put you in the code when you push OK to debug?

I suspect that it is picking up an empty value and then trying to split it into first and last name.

ASKER

list=Trim(strArray(1))

Did you copy and paste the code in?

That should be LAST=trim(strArray(1)), so I wasn't sure if you mistyped in or it was just a typo in your response.

ASKER

Last = Trim(strArray(1)) That is what I have. Still getting same error

Ok... I don't know how much you know about VB, but is there any way you can tell me what "a" and "selectname" equal when this error occurs. Personally, I would put a stop(click on the gray margin next to the line so a red dot appears) on the "Last = Trim(strArray(1))", and then when the program runs it will stop there, you can then hover your mouse over the variable to look at what it contains.

Also, in the declaration of variable section at the top of the button, put a "2" in the parenthesis and see if that helps also. To look like this:
Dim strArray(2) As String

ASKER

a=0 and selectname = "" I tried the 2 but it made the string unavailable.

ASKER

the error now reads can't assign to array

Ok.... I'd change it back to the original declaration format. I will post a link to a working version of this exact code tomorrow when I can get to work tomorrow and can put it up on the ftp. The link will be posted here.

Ok... here is the list box example I told you I'd post. Open it up and let me know if there are any problems.

Thanks,
http://members.cox.net/~tinlemon/ListBox.zip

ASKER

Still getting error "Runtime error '9'
subscript out of range

I changed the list1 name to lstNames to reflect the name of my list box. That is the only change I have made. On debugging it took me to this line:
Last=Trim(strArray(1))
and when running cursor over it it showed this information:
strArray(1)=<subscript out of range>

Frustrating isn't it?

ASKER

I did change the syle of loading the list of names to the list box (lstNames) to this code in a module. I don't think it will make any difference but:
Public NameArray(39) As String

Sub Loadnames()
i = 0
'Open "names.txt" For Input As #1
Open "D:\aiu\class6\unit5\finalgroup1\Group 1\names.txt" For Input As #1

Do While Not EOF(1)
Line Input #1, N
If N <> "" Then
Debug.Print N
NameArray(i) = N
i = i + 1
End If
Loop
Close #1
End Sub

Well..... it seems as though the array is the problem. I don't know why it's not working on your computer, but try this:

Check your Project --> References.
I have checked:
- Visual Basic For Applications
- Visual Basic runtime object and procedures
- Visual Basic objects and procedures
- OLE Automation

Make sure those are checked. Also, make sure you have the newest service pack installed, I am using Service Pack 5.

If this doesn't resolve, I would start out trying to trouble shoot that error... type the error in google perhaps and see what you get. The code I provided works perfect, and does exactly what you are after. Maybe you could use it and try to recreate it piece by piece in your own words. Print it out, and then work through it little by little. Sorry I couldn't finish this up for you, but it's hard to trouble shoot an error that isn't occuring on my side.. ya know.

Good luck with it... and if you have any questions about the code let me know.

It looks as if the problem now is that the first item, at least, that you try to read from the ListBox has no spaces and is probably is a zero-length string.

It might be worth just doing a debug.print on each item, omitting any further processing that gives run-time errors, just to re-assure yourself that what you get out is what you thought you put in.

This will clarify whether you are having a problem with the Split function or the Listbox control.

Cheers

Oh... actually... maybe you should try...

Change the value in the FOR loop to read:

For a = 1 To CountList

That might do the trick right there, just verify that it is picking the first value still, and not the second value.

Also, I have countlist defined as (list1.listcount - 1)... if you are not getting the last value take out the -1 just so you have

countlist = list1.listcount

I hope this works!

Also, DOES the first item have spaces in it? If it doesn't, Graham is right, it won't work. We'll have to create a an IF condition.

ASKER

The first name is seperated from the second name with a space

Ok... then try the For loop modification listed above.

ASKER

Private Sub CmdA_Click(Index As Integer)
Dim selectname As String
Dim indexpos As Integer
Dim strTest As String
Dim strArray() As String
Dim intCount As Integer
Dim First As String
Dim Last As String
Dim a As Integer
Dim CountList As Integer
Dim FirstinList As Integer

FirstinList = 0

CountList = lstNames.ListCount
For a = 1 To CountList

selectname = lstNames.List(a)
strArray = Split(selectname, " ")

Last = Trim(strArray(1))
First = Trim(strArray(0))

If Left$(Last, 1) = "M" Then
intCount = intCount + 1
If FirstinList = 0 Then
lstNames.Selected(a) = True
FirstinList = 1
End If

End If

Next a

MsgBox intCount
End Sub

This is what I have. I have made the changes you suggested, but still receiving runtime error focused around the array, subscript out of range runtime error 9. I did have the value of the first M in the last name, although I don't understand where in the code it is pointing at M, could you please explain?

So, assuming it is stopping on the line:
Last=Trim(strArray(1))
what is the value of the counter, a and of selectname when it errors?

ASKER

countlist=40, selectname="" and a=40

So it looks as if it is going right to the end of the list and finding an empty string there. The code is not set up to expect the unexpected. It might be better to declare an undeminsioned array and to expand it to the size of the data

Public NameArray() As String '<---
Sub Loadnames()
i = 0
'Open "names.txt" For Input As #1
Open "D:\aiu\class6\unit5\finalgroup1\Group 1\names.txt" For Input As #1

Do While Not EOF(1)
Line Input #1, N
If N <> "" Then
Debug.Print N
Redim Preserve NameArray(i)
NameArray(i) = N
i = i + 1
End If
Loop
Close #1
End Sub

ASKER

NameArray(i) = N
is now getting the error subscript out of range

undeminsioned ? Why are typos invisible until it's too late.

ASKER

where do you see that?

ASKER

oh I get it, geeze, can't see the forest for the trees anymore.

Does this happen straight away when i = 0?
Also, is the undimensioned array the one you are using or is there one with more local scope? To find out, put the cursor over the NameArray on the failing line, right-click and choose Definition from the pop-up menu.

ASKER

yes, the form won't open now. This is where the line popped on definition: Public NameArray() As String

You don't have an Option Base 1 statement in your declarations by any chance?

ASKER

no, don't even know what that is.

My last submission has disappeared.
Do you have Option Base 1 in any of your Declarations sections?
I'm going off-line now (I'm in UK), I'll be back in my morning. It is my birthday today, though, so there may be a bit of tardiness tomorrow.

After looking at this, it seems as though it is probably looping through and doing everything just fine, except when it gets to the last value (#40) it is picking up a blank entry. First, count the # of names you have... if you have 39 then that is your problem.. the loop is incrimenting 1 too many times. I had a similar problem, which is why I had to subtract 1 from the CountList #.

ASKER

good point I will try adjusting that.

ASKER

no it is 39. That is correct. Error still present.

A few comments up the page you said:
"countlist=40, selectname="" and a=40 "

This is saying that you are counting 40 names, and that the loop is on the 40th name, which is empty.

If you are now saying you have 39 names, then this loop is incorrect. You need to either start the loop on 1

For a = 1 to CountList

OR

redefine the countlist to be:
Countlist = List1.ListCount - 1
For a = 0 to Countlist

Then.... make sure you are getting ALL values, so check and see that the first name in the list and the last name in the list are both present.

ASKER

I see. There are 39 in the list, but it is returning a count of 40, somewhere along the line it is adding another line. So the stringarray is defined correctly with 39 values, but the code along the way is adding one.

Exactly! Bottom line, you have make the loop only go 39 times, not 40, not 38... 39!

ASKER

Ok but it is set as you see now:
Private Sub CmdA_Click(Index As Integer)
Dim selectname As String
Dim indexpos As Integer
Dim strTest As String
Dim strArray() As String
Dim intCount As Integer
Dim First As String
Dim Last As String
Dim a As Integer
Dim CountList As Integer
Dim FirstinList As Integer

FirstinList = 0

CountList = lstNames.ListCount - 1
For a = 0 To CountList

selectname = lstNames.List(a)
strArray = Split(selectname, " ")

Last = Trim(strArray(1))
First = Trim(strArray(0))

If Left$(Last, 1) = "M" Then
intCount = intCount + 1
If FirstinList = 0 Then
lstNames.Selected(a) = True
FirstinList = 1
End If

End If

Next a

MsgBox intCount

End Sub

the module is set as you see:
Public NameArray(39) As String

Sub Loadnames()
i = 0
'Open "names.txt" For Input As #1
Open "D:\aiu\class6\unit5\finalgroup1\Group 1\names.txt" For Input As #1

Do While Not EOF(1)
Line Input #1, N
If N <> "" Then
Debug.Print N
NameArray(i) = N
i = i + 1
End If
Loop
Close #1
End Sub

and I am still getting the error

Try combinations of this:

Current:
CountList = lstNames.ListCount - 1
For a = 0 To CountList

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CountList = lstNames.ListCount - 1
For a = 1 To CountList

CountList = lstNames.ListCount
For a = 0 To CountList

CountList = lstNames.ListCount
For a = 1 To CountList

See if any of those combos work. If they do, we can go from there.

ASKER

CountList = lstNames.ListCount - 1
For a = 1 To CountList

first try worked. No more error. However the list box is pointing to the wrong thing, it is supposed to point to the first M in the list.

What is it pointing to then?

ASKER

That didn't reflect the gratitude and overwhelming WHOOO HOOO I felt.

Explanation of first M identifier, and count... line by line:

If Left$(Last, 1) = "M" Then
intCount = intCount + 1
If FirstinList = 0 Then
lstNames.Selected(a) = True
FirstinList = 1
End If

End If

Next a

MsgBox intCount

Explanation:
'takes the last name, selects the first letter of that name and compares it to our determined value. In our case, M. If the first letter is not M, then the following statements are skipped
-->If Left$(Last, 1) = "M" Then

'if we are this far, the first letter of the last name is M. intCount counts how many M's we have. Each time it gets this far it adds 1 to the previous M count #.
--> intCount = intCount + 1

'Then, Firstinlist is predifined to equal 0. if it gets this far and it equals 0, which is the case when the first M is found, it enters this IF statement. if Firstinlist equals anything besides 0, this is skipped.
--> If FirstinList = 0 Then

'selects the first M value name in the list box. you will see it in blue.
--> lstNames.Selected(a) = True

'sets the variable Firstinlist to 1, making sure that the firstinlist IF statement isn't entered when another M is found.
--> FirstinList = 1
End If

End If

Next a

MsgBox intCount

ASKER

You did a great job. It works perfectly and I can't thank you enough. This was quite a struggle and you can imagine how a newbie like me felt.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I am so glad you didn't give up on me. This is the kind of help a newbie like me needs. Your explanations and assistance made a real difference. Thank you so much, I hope some day I will be able to get some points and help someone else.