No, this code does'nt open "Find and Replace" dialogue box with "Workbook" in Within: text box.
But I have found a suitable one while surfed the net. This will open a dialogue box that will search for your text right through the workbook
Sub FindEverywhere()
' Prompt the user for the searchText
Dim searchText As String
searchText = InputBox("Find what:", "Search All Worksheets")
' If the searchText is NOT an empty string, begin the search
If Not searchText = "" Then
Dim r As Range
Dim findNext As Integer
findNext = vbYes
Dim i As Integer
' Use For loop, not a For Each loop, as it's the only way to handle hidden/macro sheets!
For i = 1 To Sheets.Count
' Loop through each sheet in the workbook. This does NOT change the activate/visible sheet.
' That will come later if we're still searching (findNext=vbYes). I'd prefer a method to
' "break" out of the For loop, but VBA doesn't have one?
If Sheets.Item(i).Visible = xlSheetVisible Then
' This sheet is visible, so let's work with it (you HAVE to skip macro sheets, so just skip all hidden sheets)
Dim sheet As Worksheet
Set sheet = Sheets.Item(i) ' don't do this until you're sure this sheet is Visible!
' The firstFind and looped vars are used to determine if we've looped thru every find on this page.
Dim firstFoundCell As String
firstFoundCell = ""
Dim looped As Boolean
looped = False
Do While findNext = vbYes And Not looped
' This is necessary to search on the right sheet (?), but it also means we'll switch to every sheet,
' including ones which do NOT contain searchText. So, if you search the whole Workbook, you'll end up looking
' at the last sheet, whether or not it contains the searchText. :-(
Sheets(sheet.Name).Select
' Here's the search!
Set r = Cells.Find(searchText, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False)
If r Is Nothing Then
' Nothing was found. Exit this WHILE loop (the FOR loop will still go to the next sheet).
Exit Do
Else
r.Activate ' Activate (select) the cell that was found
' Check/Set the firstFoundCell and the looped variable.
If firstFoundCell = "" Then
sheet.Activate
firstFoundCell = r.Address
Else
' We've found at least one cell already. Check to see if we've looped back to the firstFoundCell.
If r.Address = firstFoundCell Then
looped = True
Else
End If
End If ' end of checking for firstFoundCell
' If we haven't looped around, prompt the user to findNext. (If we have looped, the WHILE loop will finish, causing the FOR loop to go to the next sheet.)
If Not looped Then
findNext = MsgBox("Find Next?", vbYesNo)
End If
End If
Loop ' end of Do While loop
End If
Next i
If findNext = vbYes Then
MsgBox ("No more matches found!")
End If
End If
End Sub
So thank you pike for your time and I hope this can be useful for anyone who wanted this, cheers
Bookmarks