Sub TextBox1_Click()
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer
myText = Worksheets("instructions").Range("K2")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
If ws.Name = "Instructions" Then GoTo myNext
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
AddressStr = .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
Found.EntireRow.Copy Destination:=Worksheets("instructions").Range("a2" & foundNum)
Worksheets("instructions").Range("A2" & foundNum) = AddressStr
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("instructions").Range("A2" & foundNum), Address:="", SubAddress:="Resistors!B5"
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
myNext:
End With
Next ws
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub