Can't select Listbox selection

  • Hi,


    I have a userform populated with over 6000 warehouse locations.
    i added a textbox where i enter a warehouse location for easy lookup in the listbox.
    when entering text, i'll search a match in the listbox.
    so far so good.
    there are 24 'locations' visible in the listbox.
    problem start when a match found, and i click on the highlighted 'location' in the listbox, the listboxt start showing the locations from the first 'location' in the list, and not the selected one.
    example: the match found is the 512 'location' in the list, but it shows 'location' 1 to 24 after I click on the 512-th item, or try to scroll down with the listbox scrollbar.


    here's my code for the textbox where i enter the search string to look for in the listbox.



    and here's the code from the listbox.



    what i like to obtain is, when i find a match, then click on the highlighted 'location' in the listbox (or scroll down with the listbox scrollbar), that the listbox doesn't get 'reset' to the first 'location' in the list.


    the variables l and bMatchFound are Public.


    I'm convinced that it can be done, just don't know how to get it working.
    Any help welcome.


    best regards,
    Ludo

  • Re: Can't select Listbox selection


    The variable L (my uppercase to differentiate it) is not declared anywhere, so it probably has a value of 0 in the procedure tbSearchLocation_Exit


    If you want to use it like that, then it should be scoped to the module


    Go do 2 Google searches


    Pearson Option Explicit
    Pearson VBA variable scope


    And read the pages by Chip Pearson


    OK - ignore that. Didn't notice the

    Quote

    the variables l and bMatchFound are Public.


    I'll have a closer look.

  • Re: Can't select Listbox selection


    O.K.


    attached you'l find a derived EXEL file.
    copied the code from Word to a Excel file.


    the file 'Locaties.xlsx' contains the warehouse locations.
    I reduced the list to 3000 locations instead of 6336 locations to get a smaller file size.


    the file Warehouse locations2.xlsm is the 'application'


    when you open this file in Excel, it adds a new tab called "Warehouse Labels" in the ribbon next to the Home tab.
    click on the "Print Labels" button.
    the code will check if the full path & filename to the file "Locaties.xlsx" exist in the local registry, if not, you have to place it in the upper textbox from the userform 'frmLabelLocFileName'.
    place in the second textbox following string: alle lokaties in 1 file
    this is the sheet name where the warehouse locations are read from (column F).
    click save & close.


    the code will import the label locations from the file 'Locaties.xlsx, sheet 'alle lokaties in 1 file'.


    you can enter a search string in the 'Search Location' textbox. The listbox will follow the input from the textbox, but once you click on the highlighted item in the listbox, it shows the first 24 locations, and not the one selected.


    to stop, click the close button.
    don't try to print, because this code is still for use with Word.


    thanks for looking at it.


    best regards,
    Ludo

  • Re: Can't select Listbox selection


    Hi,


    it seems to be that i found the solution.
    I need to use the Selected property of the listbox.


    here's the code of the modified textbox events using the Selected property in the userform frmSelectLabels:


    [code]
    Private Sub tbSearchLocation_Change()
    Dim lLocCount As Long 'number of warehouse locations in the listbox
    Dim SearchCriteria As String 'search string
    Dim iLength As Integer 'length of the search string
    Dim L As Long 'counter

    lLocCount = Me.ListBox1.ListCount 'lLocCount= the number of locations
    With Me.tbSearchLocation
    SearchCriteria = .Value
    iLength = Len(.Value)
    'if next character entered for search, reset the bMatchFound flag
    If iSearchStringLenth < iLength Then
    bMatchFound = False
    End If
    iSearchStringLenth = iLength 'save searchstring length for next search
    .Value = UCase(.Value)
    End With
    'loop through the listbox for a match
    For L = 0 To lLocCount - 1
    If bMatchFound = True Then Exit Sub '24/03
    With Me.ListBox1
    If Left(.List(L), iLength) = SearchCriteria Then
    .ListIndex = L 'highlight the match in the listbox
    .Selected(L) = True
    lMatchListIndex = L 'save the listindex
    bMatchFound = True
    End If
    End With
    Next
    End Sub



    Private Sub tbSearchLocation_Enter()
    With Me.tbSearchLocation
    .BackColor = cYellow
    End With
    End Sub



    Private Sub tbSearchLocation_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.tbSearchLocation
    .BackColor = vbWhite
    End With
    With Me.ListBox1
    .Selected(lMatchListIndex) = True
    End With
    bMatchFound = True
    End Sub
    [\code]


    and here's the code from the listbox events
    [code]
    Private Sub ListBox1_Change()
    Dim lCntr As Long
    Dim lCntr2 As Long
    Dim lSelections As Long 'number of selected items in listbox (multi select)



    Select Case PrintMode
    'print single selection
    Case Is = cPrintSingleLabel
    ReDim Preserve sLocations(0)
    'we get only the selection if an item from the listbox is selected
    'then we enable the 'Verder' button.
    With frmSelectLabels
    If .ListBox1.ListIndex <> -1 Then
    sLocations(0) = .ListBox1.List(frmSelectLabels.ListBox1.ListIndex)
    .cmdNext.Enabled = True
    End If
    End With
    'print selection
    Case Is = cPrintSelection
    For lCntr = 0 To frmSelectLabels.ListBox1.ListCount - 1
    If frmSelectLabels.ListBox1.Selected(lCntr) = True Then
    ReDim Preserve sLocations(lCntr2)
    sLocations(lCntr2) = frmSelectLabels.ListBox1.List(lCntr)
    lCntr2 = lCntr2 + 1
    frmSelectLabels.cmdNext.Enabled = True
    End If
    Next
    End Select
    End Sub



    Private Sub ListBox1_Enter()
    If bMatchFound = True Then Exit Sub
    With Me.ListBox1
    .BackColor = cYellow
    End With
    End Sub



    Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
    .BackColor = cWhite
    End With
    bMatchFound = False
    End Sub
    [\code]


    following variables are public
    [code]
    Public Const cYellow = &H80FFFF
    Public Const cWhite = &H80000005



    Public lMatchListIndex As Long 'listbox1 list index for search match
    Public bMatchFound As Boolean 'True if a match found
    [\code]


    And the variable iSearchStringLenth is defined ON TOP of the userform frmSelectLabels.
    [code]
    Option Explicit
    Dim iSearchStringLenth As Integer
    [\code]


    thanks for looking at it.


    Ludo

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!