Solved

Adjusting the ComboBox list contents automatically

Posted on 2001-06-08
5
314 Views
Last Modified: 2008-02-01
I have a small program used as a Mate/Demate log for connectors at work. The list of all the connectors is quite big and varied so all i need is some additions to this coding (below) to shorten the drop down list to all then entries that match whats been typed in so far.
For example if i type in a 5 first then click the drop down arrow i would only like to see the entries starting with a 5, but then iff i add a 9 to the 5 making the box say 59 i would then like the drop down box to reduce its contents to the entries starting 59.
I have seen this done on may programs and web pages so i know it is possible i just cant find how to do it in the VB help files  :(


Here is an extract of some of the coding i have already done.


Private Sub Partbox_STATUS(enable As Boolean)
Select Case enable
Case False
    Partbox.Visible = False
    PartLabel.Visible = False
    Partbox.Enabled = False
    Partbox.Text = ""
    Portbox_STATUS (False)
Case True
    Dim name As String
    Dim clock As String
    Dim counter As Integer
    Dim endloop As Integer
    Dim check As Integer
   
    endloop = 0
    counter = 1
    Partbox.Clear
    Do
        counter = counter + 1
        part = Sheets(projectbox.Text).Range(rowCol(counter, 1))
        port = Sheets(projectbox.Text).Range(rowCol(counter, 2))
       
        If Len(Trim(port)) Then
            If Len(Trim(part)) Then
                Partbox.AddItem Trim(part)
            End If
        Else
            endloop = 1
        End If
    Loop Until endloop
   
    Sheets(projectbox.Text).Select
    Range("E2").Select
   
    Partbox.Visible = True
    PartLabel.Visible = True
    Partbox.Enabled = True
    Partbox.SetFocus
End Select
End Sub

Private Sub partbox_Change()
If Partbox.MatchFound Then
Portbox_STATUS (True)
End If
End Sub

Private Sub partbox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case Is = 13, Is = 9
Partbox.Text = UCase(Partbox.Text)
If Partbox.MatchFound Then
    Portbox_STATUS (True)
    portbox.SetFocus
    Else
        found = -1
        For loopcount = 0 To Partbox.ListCount - 1
            If Trim(Partbox.List(loopcount)) = Trim(UCase(Partbox.Text)) Then
                found = loopcount
            End If
        Next loopcount
             
        If found >= 0 Then
            Partbox.ListIndex = found
            Portbox_STATUS (True)
        Else
            Partbox.Text = ""
            Portbox_STATUS (False)
        End If
    End If
End Select
End Sub

Private Sub partbox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Partbox.Text = ""
End Sub

Private Sub partbox_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Portbox_STATUS (False)
End Sub




I have looked at the MatchEntry properties options for the ComboBox and selecting anything other than MatchEntryNone make the program select the first match (if i hit 5 then it selects the first entry starting with a 5 and goes onto the next box).

Pls could anyone suggest anything??.


Thanks.
0
Comment
Question by:craigewens
5 Comments
 
LVL 13

Expert Comment

by:samic400
ID: 6167068
What about keeping all of your values in a database and then based on whatever you need you can go out and get only those needed from access or SQL Server.

like sql statements - "select * from <table> where <field1> like "5*"

or

"select * from <table> where <field1> like "59*"

and so on.

This is the way I do these types of things for my ASP and VB projects. Just a thought. Maybe you cannot in your situation but it might work for you.
0
 
LVL 2

Author Comment

by:craigewens
ID: 6167204
Ummmm im sorry to say but most of what you just wrote sounded like:

Blahh blahh blah VB projects Blaa Blaaah.... sorry  :)

I'll explain a little more....The data (partbox) is all currently in column A of an excel sheet, beside the part number in column B is a list of the relative ports on that particular part (portbox), so the user selects the project (projectbox) which moves to the correct excel sheet, then they are presented with the partbox combo box to select a part, on selecting a part they then get the portbox combo box with all the ports for their selected part.
The coding im after will/should allow the user to choose the part they require from a slightly reduced list depening on them typing in a few of the starting letters/numbers of the parts particular id number.

Hope that makes a little more sence.


Craig.
0
 
LVL 1

Expert Comment

by:Ranger2081200
ID: 6167234



IT sounds like you need to filter your list right before your additem.
If I understand your question correctly try the following.

--------------------------------------------------------
x = len(partbox)  'set x equal to number of characters in partbox.

If Len(Trim(port)) Then
   If Len(Trim(part)) Then
      if left(part, x) = partbox.text then
         Partbox.AddItem Trim(part)
      end if
   End If
Else
   endloop = 1
End If
--------------------------------------------------------

The left(part, x) should compare the first x characters of part with the text in partbox and then add item if it matches.

I see were you set Portbox_STATUS but not Partbox_STATUS?

Later...
0
 
LVL 2

Author Comment

by:craigewens
ID: 6167614
Thx for the idea, but what would happen if the length of the data in partbox varies from 1 to like 100??, i'm trying not to introduce possible crash situations in future use.
0
 
LVL 5

Accepted Solution

by:
gwgaw earned 100 total points
ID: 6170397
If you are changing the visibilty of the portbox, then when you set it to
false also set it's enabled property to false. When you want to show
it set it's enabled property to true after making it visible. That
way when a match is found in partbox it will keep the focus. Then for
your partbox change event try something like this with MatchEntry set
to frmMachEntryComplete.

Private Sub partbox_Change()
'used to stop recursion when clearing partbox.text
Static bNoMatch As Boolean
If bNoMatch Then
    Exit Sub
End If
If partbox.MatchFound Then
    Portbox_STATUS (True)
Else
    Portbox_STATUS (False)
    bNoMatch = True
    partbox.Text = ""
    bNoMatch = False
End If
End Sub


To reduce the items in partbox do something like...

If partbox.Text <> "" Then
    For i = partbox.ListCount - 1 To 0 Step -1
        If Left(partbox.List(i), Len(partbox.Text)) <> partbox.Text Then
            partbox.RemoveItem (i)
        End If
    Next i
End If

The number of items will be reduced as the user types. If the user hits
backspace or deletes characters then you will need to add items
back into partbox. This could get quite complicated. But if you put
the parts in a collection called parts(or whatever) then you could do
something like this. This would also be in the partbox_Change sub.

Dim part As Variant, stPart As String
stPart = partbox.Text
partbox.Clear
'when you clear the partbox, the partbox.text will also be cleared
'so you need to be able to reset it
partbox.Text = stPart
For Each part In parts
    If Left(partbox.List(i), Len(partbox.Text)) = partbox.Text Then
        partbox.AddItem parts(partbox.List(i))
    End If
Next

Iterating thru a collection is a lot faster than iterating thru
listitems. Especially if you have a large number of parts.


To get the parts into the parts collection, in your Partbox_STATUS sub...

       If Len(Trim(port)) Then
           If Len(Trim(part)) Then
               parts.Add , Trim(part)
           End If
       Else
           endloop = 1
       End If
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now