Application Error 1004 on Search Update Function

Hi again,

I have this code which was contributed by one of the guru's here however when this code runs it give the error message Application Error 1004 when doing search update. The code must detect that if the entered employee id is not on the list error message "Not Found in database" message should appear. Also if the user attempted to enter symbols or alpha characters it should prompt him a message that says "Please use Numerical Values Only". Please help.

Private Sub searchupdate()
    Dim MatchCell As Range
    Dim strFind, FirstAddress As String   'what to find
    Dim rSearch As Range  'range to search
    Dim f As Integer
    
    'Search Range is the ID numbers column
      Set rSearch = ActiveSheet.Range("C10", Cells(Rows.Count, "C").End(xlUp))
      
     'ID number to find
      strFind = TextBox1.Value
      
      'Allways set All the Named Argumnets for the First Search.
      'The system "Find" function uses these settings also.
        With rSearch
          Set MatchCell = .find(What:=strFind, _
                                          LookIn:=xlValues, _
                                          LookAt:=xlWhole, _
                                          SearchOrder:=xlRows, _
                                          SearchDirection:=xlNext, _
                                          MatchCase:=False)
            
                If Not MatchCell Is Nothing Then    'found it
               'load entry to form
                
                lblemployee.Caption = MatchCell.Offset(0, 1).Value 'Employee name
                lblshift.Caption = MatchCell.Offset(0, -1).Value  'shift
                lblcoach.Caption = MatchCell.Offset(0, -2).Value  'coach
                
                ListBox1.RowSource = MatchCell.Offset(0, 2).Resize(1, 10).Address
                'ListBox1.ListIndex = 0
                FirstAddress = MatchCell.Address
                CurRow = MatchCell.Row
                TextBox2.SetFocus
                               
                End If
                        If TextBox1.Value = "" And TextBox2.Value = "" Then
                        MsgBox "Please enter Employee ID", vbExclamation + vbOKOnly, "Invalid Input"
                        TextBox1.SetFocus
                        End If
               
         End With
                
End Sub

Regards,

Stoey

Any idea what am I missing here?

Thanks,

Stoey

hi snb,

first is i do appreciate your assistance and the solution you provided. well the reason for which I used a textbox is because I have more than 200 employee ids. so if i were to use a combobox, then there would be a long list when the user will click the drop down though I know that they can also manually enter the employee id with the use of combo box. another thing is that, if I were to use a combobox, most of the employee ids will be revealed...that i would like to keep as confidential to other employees...any other approach in mind?

thanks,

stoey

oh pardon me but i dont get what do you mean by that...uhm well all what i trying to accomplish is that if the user enters an employee id which is not on the database then it would display a message that "employee id does not exist" something like that...im sorry if Im causing you pain here...but i do appreciate your ideas

hi mordred,

hmm..how do i do that? can you give me a macro if this is not too much for you to ask?

thanks,

stoey