Dim ws As Worksheet
Dim xStr As Variant
Dim fCell As Range
If frm_findjob.TextBox1.Value = "" Then
MsgBox "Sorry, Find box empty"
Exit Sub
End If
If frm_findjob.ComboBox1 = "FInd" Then
If frm_findjob.TextBox1.Value = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
Set fCell = ws.Cells.Find(frm_findjob.TextBox1.Value)
On Error GoTo 0
If Not fCell Is Nothing Then
On Error GoTo msgwbox
frm_findjob.TextBox6.Text = ws.Name
frm_findjob.TextBox2.Text = Sheets(ws.Name).Range("A:A").Find(frm_findjob.TextBox1.Value).Offset(0, 0).Text
frm_findjob.TextBox3.Text = Sheets(ws.Name).Range("A:A").Find(frm_findjob.TextBox1.Value).Offset(0, 2).Text
frm_findjob.TextBox4.Text = Sheets(ws.Name).Range("A:A").Find(frm_findjob.TextBox1.Value).Offset(0, 3).Text
frm_findjob.TextBox5.Text = Sheets(ws.Name).Range("A:A").Find(frm_findjob.TextBox1.Value).Offset(0, 4).Text
Exit Sub
End If
Next ws
ElseIf frm_findjob.ComboBox1 = "Name" Then
If frm_findjob.TextBox1.Value = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
Set fCell = ws.Cells.Find(frm_findjob.TextBox1.Value)
On Error GoTo 0
If Not fCell Is Nothing Then
On Error GoTo msgwbox
frm_findjob.TextBox6.Text = ws.Name
frm_findjob.TextBox2.Text = Sheets(ws.Name).Range("B:B").Find(frm_findjob.TextBox1.Value).Offset(0, 0).Text
frm_findjob.TextBox3.Text = Sheets(ws.Name).Range("B:B").Find(frm_findjob.TextBox1.Value).Offset(0, 1).Text
frm_findjob.TextBox4.Text = Sheets(ws.Name).Range("B:B").Find(frm_findjob.TextBox1.Value).Offset(0, 2).Text
frm_findjob.TextBox5.Text = Sheets(ws.Name).Range("B:B").Find(frm_findjob.TextBox1.Value).Offset(0, 3).Text
Exit Sub
End If
Next ws
End If
msgwbox:
MsgBox "Sorry, I couldn't find that."
Exit Sub
'IF we get to here, we didn't find it
End Sub
Private Sub CommandButton1_Click()
FindSomething
End Sub
Private Sub CommandButton2_Click()
With frm_findjob
.TextBox1 = ""
.TextBox2 = ""
.TextBox3 = ""
.TextBox4 = ""
.TextBox5 = ""
.TextBox6 = ""
End With
End Sub
0 ความคิดเห็น