Searching a Workbook by Default

When you display the Find tab of the Find and Replace dialog box (pressing Ctrl+F is the easiest way), Excel makes certain assumptions about what exactly you want to search. What you want to search is dictated by the setting of the Within drop-down list. When you first display the Find and Replace dialog, Within is set to Sheet, by default. (Click here to see a related figure.) This setting is true regardless of whether you select one worksheet or multiple worksheets prior to displaying the dialog box.

If you want the Within drop-down list to default to Workbook (instead of Sheet), there is no way to specify this in Excel. You can take some solace in the fact that the setting of the Within drop-down list is persistent for the current session with Excel. In other words, if you set it to Workbook, complete your search, and later display the Find tab of the Find and Replace dialog, box then Within is still set to Workbook.

It is interesting that there appears to be no way tackle this issue using a macro, either. This is because Excel doesn't provide a way for a macro to display and modify the settings in the Find and Replace dialog box. Many dialog boxes can be displayed, but not the Find and Replace. Instead, VBA allows you to display an older version of the Find dialog box (Click here to see a related figure.) , using this code:

Sub ShowFind()
    Application.Dialogs(xlDialogFormulaFind).Show
End Sub

Unfortunately, this version of the Find dialog box does not have a control that allows you to specify the scope of the search, as can be done with the Within drop-down list in the Find tab of the Find and Replace dialog box.