On Error Resume Next should not be used when testing code.
There are several not quite obvious changes:
mysheet as Worksheets to Mysheets as Sheets.
Activate removed
xlWhole had to be retyped.
Columns(B) to Columns("B")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'---------------
Private Sub CommandButton2_Click()
Dim Findstring As String
Dim Rng As Excel.Range
Dim Mysheets As Excel.Sheets
Dim objSht As Excel.Worksheet

Findstring = InputBox("Enter the box reference", " Blond and Proud")
If Len(Findstring) = 0 Then Exit Sub

Set Mysheets = Worksheets(Array("Sheet 2", "Sheet 4", "Sheet 6", "Sheet 8"))

For Each objSht In Mysheets
'On Error Resume Next
Set Rng = objSht.Columns("B").Find(What:=Findstring, After:=Range("B5"), _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
'On Error GoTo 0
If Not Rng Is Nothing Then
Application.Goto Rng, True
MsgBox "Found on " & objSht.Name & " in cell " & Rng.Address(False, False) & _
". ", vbInformation, " Blond and Proud"
Exit Sub
End If
Next 'objSht
MsgBox "No box found. ", vbExclamation, " Blond and Proud"
End Sub
'----------


"Blondegirl"wrote in message...
Hi, I'm a newbie to VBA and am struggling with this bit of coding so I
would really appreciate any help! I need to be able to search just
certain worksheets in the workbook as it's important that the find does
not extend to the other worksheets. The user enters the reference
number in a textbox on "Sheet 1" and it should look through just
certain sheets only and where it finds the matching value, to make that
the active cell. I don't know what I need to add to make my coding work
here. At the moment it won't find any matches and keeps making "sheet
2" the active sheet. (for speed it only needs to search column B, from
B5 onwards, on these sheets)

Private Sub CommandButton2_Click()
On Error Resume Next
Dim Findstring As String
Dim Rng As Range
Dim mysheet As Worksheets
Findstring = TextBox1.Text
Worksheets(Array("Sheet 2", "Sheet 4", _
"Sheet 6", "Sheet 8")).Select

For Each mysheet In Worksheets

If Findstring < "" Then
Set Rng = Columns(B).Find(What:=Findstring, _
After:=Range("B5"), _
LookIn:=xlValues, _
LookAt:=x1Whole, _
MatchCase:=False).Activate
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "No box found"
TextBox1 = ""
End If
Else
MsgBox "Please enter the box ref"
End If
Next mysheet
End Sub