Searching Excel sheet from VB

[net_buster]

Ive written some basic code from reading the values in certain cells in vb and putting them into a combo box. Now ive got a text box for membership numbers, (this is just an exercise im not from a company or anything ) How can i get VB to search the excel sheet for that number?
If the number is found then open formx else "Sorry you have enter the number incorrectly"
Cheers

Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")
 
appExcel.DisplayAlerts = False
appExcel.Visible = False
appExcel.ScreenUpdating = True
appExcel.Workbooks.Open FileName:="C:\names.xls"
appExcel.Worksheets("sheet1").Select
 
Private Sub Command1_Click()
 
If Form1.NamesCombo.ListIndex < 0 Then
MsgBox "You must select a student!", vbExclamation, "Error"
Exit Sub
End If
 
On Error GoTo errhandle:
 
Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")
 
Private Sub Command1_Click()
appExcel.DisplayAlerts = False
appExcel.Visible = False
appExcel.ScreenUpdating = True
appExcel.Workbooks.Open FileName:="C:\names.xls"
appExcel.Worksheets("sheet1").Select
 
appExcel.Save
appExcel.Quit
 
MsgBox "The records have been updated"
 
Exit Sub
errhandle:
Set appExcel = Nothing
MsgBox "An error has occured: " & Err.Description
appExcel.Quit
 
End Sub

This is the code i am using for opening and closing VB once data has been entered. Now what i want to do, once a value is typed into a text box i want to be able to search the excel sheet for that exact value, if it is not found then "error message" else load formX
Regards

[RobDog888]

You have two Command1_Click events and only one End Sub. also, code outside any procedures.

You need to use the .Find method off of the Range object.

With Worksheets(1).Range("A1:A500")
        Set c = .Find(UserForm1.txtSearch.Text, LookIn:=xlValues)
        If Not c Is Nothing Then
            MsgBox c.Value
        End If
    End With