+ Reply to Thread
Results 1 to 3 of 3

Listbox on a userform issue

Hybrid View

  1. #1
    Registered User
    Join Date
    12-28-2006
    Posts
    97

    Listbox on a userform issue

    I have a listbox on a user form that populates from a worksheet. When I click on a specific itme in the list box it loads that specif data onto the userform textboxes. This works perfectly except for one snag. The 1st item on the list box never searches. I am sure this is just a listbox setting but can figure out which. I will post my code below for reference
    Private Sub ListBox2_Click()
        'On Error GoTo err_handlerL
        Dim lngRow As Long
        Dim rngRec As Range
        Dim lngItem As Long
        Dim r As Long
        Sheets("State2").Select
        
        If Me.ListBox2.ListIndex = -1 Then    'not selected
            MsgBox " No selection made"
        ElseIf Me.ListBox2.ListIndex >= 1 Then    'User has selected
             
         With Application
            .ScreenUpdating = True
            .GoTo Sheets("State2").Range("A2"), True
        End With
       Dim strFind As String    'what to find
        Dim FirstAddress As String
        Dim rSearch As Range  'range to search
        Dim F As Integer
      
       
    
        '###RB: explicitly defined ranges
        With Sheets("State2")
            Set rSearch = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
        End With
        'imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
        strFind = Me.ListBox2.Value    'what to look for
        With rSearch
            Set c = .Find(strFind, LookIn:=xlValues)
            If Not c Is Nothing Then    'found it
                c.Select
                
            With Me
                For lngItem = 1 To 3
                    .Controls("TextBox" & lngItem).Value = c.Offset(0, lngItem - 1).Value
                       
                        'load textboxes values from a cell
                      
    ComboBox3.Value = c.Offset(0, 3).Value
    ComboBox5.Value = c.Offset(0, 4).Value
    ComboBox6.Value = c.Offset(0, 5).Value
    
    
                    Next
    
    
                    '.cmbAmend.Enabled = True     'allow amendment or
                    '.cmbDelete.Enabled = True    'allow record deletion
                    '.cmdAdd.Enabled = True      'Changed to allow 11-2-09 don't want to duplicate record
                    F = 0
                End With
                FirstAddress = c.Address
                Do
                    F = F + 1    'count number of matching records
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> FirstAddress
                If F > 1 Then
                    Select Case MsgBox("There are " & F & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
                        Case vbOK
                            FindAll
                        Case vbCancel
                            'do nothing
                    End Select
                    
                End If
            Else: MsgBox strFind & " not listed"    'search failed
            End If
        End With
        '###RB
        With Sheets("state2")
            If .AutoFilterMode Then .Range("A2").AutoFilter 'a3
        End With
      End If
      
      
        Exit Sub
    err_handlerF:
        MsgBox _
                "An unexpected error has been detected" & Chr(13) & _
                                                        "Description is: " & Err.Number & " , " & Err.Description & Chr(13) & _
                                                        "Module is: Find_click" & Chr(13) & _
                                                       "Please note the above details and email the error to [email protected]"
        Sheets("Intro").Select
        
    End Sub
    
    
    Sub LB()
    Dim cell As Range
        Dim rng As Range
        Call Provider77A
       ListBox2.Clear
        With Sheets("tempsheet")
            Set rng = .Range("A2", .Range("A2").End(xlDown))
        End With
        
        For Each cell In rng.Cells
            With Me.ListBox2
                .AddItem cell.Value
                .List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = cell.Offset(0, 3).Value
            End With
        Next cell
    End Sub
    Sub FindAll()
    
    Sheets("state2").Select
    
        
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    '###RB: explicitly defined ranges
        With Sheets("state2")
            .Select
            Set rFilter = .Range("a1", .Range("d" & Rows.Count).End(xlUp)) 'a2
            Set rng = .Range("a1", .Range("a" & Rows.Count).End(xlUp))
        End With
        strFind = Me.TextBox1.Value    'what to look for
        '###RB: changed from "With Data" to be in a single line
        '###RB: changed from this (to the next line)...        If Not .AutoFilterMode Then .Range("A2").AutoFilter
        If Not Sheets("state2").AutoFilterMode Then rFilter.AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        
        Me.ListBox1.Clear
       For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Row '1
            End With
        Next c
        Worksheets("state2").AutoFilterMode = False
    End Sub
    Sub DisplayData(Data As Range)
        On Error GoTo err_handlerdd
        Dim lngRow As Long
        Dim rngRec As Range
    
        Dim lngItem As Long
        Set rngRec = Rows(lngRow)
            With Me
                For lngItem = 1 To 3
                    .Controls("Textbox" & lngItem).Value = rngRec.Cells(1, lngItem).Value
                    .Controls("ComboBox3").Value = rngRec.Cells(1, 4).Value
                    .Controls("ComboBox5").Value = rngRec.Cells(1, 5).Value
                    .Controls("ComboBox6").Value = rngRec.Cells(1, 6).Value
                    
                Next
    
            '.cmbAmend.Enabled = True      'allow amendment or
            '.cmbDelete.Enabled = True     'allow record deletion
            '.cmdAdd.Enabled = True       'Allowed 11-4-09 don't want duplicate
        End With
        Exit Sub
    err_handlerdd:
        MsgBox _
                "An unexpected error has been detected" & Chr(13) & _
                                                        "Description is: " & Err.Number & " , " & Err.Description & Chr(13) & _
                                                        "Module is: DisplayData" & Chr(13) & _
                                                        "Please note the above details and email the error to [email protected]"
        Sheets("Intro").Select
    End Sub

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Listbox on a userform issue

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,298

    Re: Listbox on a userform issue

    The ListIndex starts at 0 but your code checks for it being >=1 so you just need to change that to >=0
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1