?
Solved

ListBox filter

Posted on 2003-03-17
54
Medium Priority
?
329 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:zorn01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 27
  • 19
  • 8
54 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8152153
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.
0
 

Author Comment

by:zorn01
ID: 8152249
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".
0
 

Author Comment

by:zorn01
ID: 8152273
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".
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:TinLemon
ID: 8153800
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??
0
 

Author Comment

by:zorn01
ID: 8153822
I would like to sort by the last name.
0
 

Expert Comment

by:TinLemon
ID: 8153835
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??
0
 

Author Comment

by:zorn01
ID: 8153851
first letter of the last name.  
0
 

Expert Comment

by:TinLemon
ID: 8154052
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
0
 

Author Comment

by:zorn01
ID: 8154150
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.
0
 

Expert Comment

by:TinLemon
ID: 8154183
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.
0
 

Author Comment

by:zorn01
ID: 8154206
list=Trim(strArray(1))
0
 

Expert Comment

by:TinLemon
ID: 8154235
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.
0
 

Author Comment

by:zorn01
ID: 8154275
Last = Trim(strArray(1))  That is what I have.  Still getting same error
0
 

Expert Comment

by:TinLemon
ID: 8154315
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
0
 

Author Comment

by:zorn01
ID: 8155202
a=0 and selectname = ""  I tried the 2 but it made the string unavailable.
0
 

Author Comment

by:zorn01
ID: 8155329
the error now reads can't assign to array
0
 

Expert Comment

by:TinLemon
ID: 8155578
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.
0
 

Expert Comment

by:TinLemon
ID: 8166783
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
0
 

Author Comment

by:zorn01
ID: 8167529
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?
0
 

Author Comment

by:zorn01
ID: 8167545
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
0
 

Expert Comment

by:TinLemon
ID: 8168525
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.

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8172218
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
0
 

Expert Comment

by:TinLemon
ID: 8173123
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!
0
 

Expert Comment

by:TinLemon
ID: 8173128
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.
0
 

Author Comment

by:zorn01
ID: 8173204
The first name is seperated from the second name with a space
0
 

Expert Comment

by:TinLemon
ID: 8173240
Ok... then try the For loop modification listed above.
0
 

Author Comment

by:zorn01
ID: 8173296
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?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8173610
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?
0
 

Author Comment

by:zorn01
ID: 8173906
countlist=40, selectname="" and a=40
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8173956
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
0
 

Author Comment

by:zorn01
ID: 8173989
NameArray(i) = N
is now getting the error subscript out of range
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8173991
undeminsioned ? Why are typos invisible until it's too late.
0
 

Author Comment

by:zorn01
ID: 8174004
where do you see that?
0
 

Author Comment

by:zorn01
ID: 8174013
oh I get it, geeze, can't see the forest for the trees anymore.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8174050
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.
0
 

Author Comment

by:zorn01
ID: 8174070
yes, the form won't open now. This is where the line popped on definition:  Public NameArray() As String
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8174198
You don't have an Option Base 1 statement in your declarations by any chance?
0
 

Author Comment

by:zorn01
ID: 8174212
no, don't even know what that is.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 8174355
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.
 
0
 

Expert Comment

by:TinLemon
ID: 8174689
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 #.  
0
 

Author Comment

by:zorn01
ID: 8174763
good point I will try adjusting that.
0
 

Author Comment

by:zorn01
ID: 8174830
no it is 39.  That is correct.  Error still present.
0
 

Expert Comment

by:TinLemon
ID: 8174890
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.

0
 

Author Comment

by:zorn01
ID: 8174966
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.
0
 

Expert Comment

by:TinLemon
ID: 8175014
Exactly!  Bottom line, you have make the loop only go 39 times, not 40, not 38... 39!
0
 

Author Comment

by:zorn01
ID: 8175114
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
0
 

Expert Comment

by:TinLemon
ID: 8175376
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.


0
 

Author Comment

by:zorn01
ID: 8175401
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.
0
 

Expert Comment

by:TinLemon
ID: 8175414
What is it pointing to then?

0
 

Author Comment

by:zorn01
ID: 8175415
That didn't reflect the gratitude and overwhelming WHOOO HOOO I felt.
0
 

Expert Comment

by:TinLemon
ID: 8175474
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
0
 

Author Comment

by:zorn01
ID: 8175632
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.
0
 

Accepted Solution

by:
TinLemon earned 200 total points
ID: 8175693
Whew.... I almost gave up on you, but some of Grahams comments sparked some different thoughts in me.  

So it's finding the first M, and counting the right amount and all that?!?!  If so, excellent!!!

If you really want to thank me, I'll gladly accept those points :) :) :)  This is the first question I ever answered, and I did so because I need some points to ask more questions myself :)
0
 

Author Comment

by:zorn01
ID: 8175907
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question