I recently found sample code online for a Student Registration Userform that I downloaded and modified for my needs. I was able to modify everything successfully to meet my needs with the exception of the "Search" function.
To use the search function the user must enter a search term, such as a student's last name, click the "Search" button and all records matching the search term will be displayed in the corresponding listbox. However, this process generates the following error message, "Compile Error: Invalid Use Of Me Keyword". I have tried modifying the code in several ways, but always end up with the same error message.
Any suggestions regarding correcting the code or even deleting the functionality the offending code references would be appreciated. See code below.
"Me Error" is Line 31
Sub Lookup()
'declare the variables
Dim rngFind As Range
Dim strFirstFind As String
'error statement
On Error GoTo errHandler:
'clear the listbox
lstLookup.Clear
'look up parts or all of full mname
With Sheet2.Range("E:E")
Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
'if value found then set a variable for the address
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
'add the values to the listbox
Do
If rngFind.Row > 1 Then
lstLookup.AddItem rngFind.Value
lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, 1)
lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, 2)
lstLookup.List(lstLookup.ListCount - 1, 3) = rngFind.Offset(0, 3)
lstLookup.List(lstLookup.ListCount - 1, 4) = rngFind.Offset(0, 4)
lstLookup.List(lstLookup.ListCount - 1, 5) = rngFind.Offset(0, 5)
End If
'find the next address to add
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If
End With
'disable payroll editing
Me.Reg4.Enabled = False
Me.cmdEdit.Enabled = False
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub
Display More