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
Bookmarks