Excel.Tips.Net ExcelTips (Menu Interface)

Searching a Workbook by Default

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 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. (See Figure 1.) 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. This setting is true regardless of whether you select one worksheet or multiple worksheets prior to displaying the dialog box.

Figure 1. The Find tab of the Find and Replace 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, at first blush, there appears to be no way tackle this issue using a macro. This is because Excel doesn't provide a way for a macro to easily display and modify the settings in the Find and Replace dialog box. Many dialog boxes can be displayed using the Dialogs collection, but not the Find and Replace. Instead, VBA allows you to display an older version of the Find dialog box, using this code:

Sub ShowFind1()
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.

There is a way to display the correct Find and Replace dialog box, but it isn't by using the Dialogs collection. Instead you need to pull up the dialog box using the CommandBars collection, which essentially displays the dialog box using a menu command. Here's how to do it:

Sub ShowFind2()
    ActiveSheet.Cells.Find What:="", LookAt:=xlWhole
    Application.CommandBars("Worksheet Menu Bar").FindControl( _
      ID:=1849, recursive:=True).Execute
End Sub

The Find method allows you to set the different parameters in the Find and Replace dialog, and then the CommandBars object is accessed to actually display the dialog box.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3170) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Searching a Workbook by Default.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Dave Kerr    04 Jun 2014, 09:34
I wrote a macro to search a workbook for a value in both formulas and cell text. It is stored in my PERSONAL.XLS file so it is available at any time.

It adds a sheet called SearchResults at the beginning of the file. Any matches are listed by sheet and cell reference. Each time you run the macro, it deletes the sheet (if it exists) and creates a new one for the next set of results. All sheets, including hidden ones, are searched.

Here is the code, which I hope you find useful.


Sub ListSearchValues()
        ' macro written by Dave Kerr, January 2014
        ' objective: search for a string in either formulae or cell values
        ' output the results in a new sheet in the same workbook
    Dim Srch As String ' search string
    Dim NS As Integer ' number of sheets in workbook
    Dim j As Integer ' loop counter
    Dim wsThis As Worksheet
    Dim i As Integer
    Dim k As Integer
    Srch = inputbox("Search value", "Find a value")
    If Len(Srch) = 0 Then ' cancel pressed or no input
        Exit Sub
    End If
    Application.ScreenUpdating = False
    On Error Resume Next ' bypass an error if it arises
    Sheets("SearchResults").Delete ' delete a search results sheet if it exists
    On Error GoTo 0
    Sheets(1).Select ' go to the first sheet
    Sheets.Add ' add a new sheet for the list of links
    ActiveSheet.Name = "SearchResults"
    Set wsThis = ActiveSheet
    Range("A1").Value = "Formula Search"
    Range("C1").Value = Srch
    Range("E1").Value = "Cell Value Search"
    i = 2
    k = 2
    NS = Sheets.Count
    For j = 2 To NS
    On Error Resume Next
        Range("A1:" & ActiveCell.SpecialCells(xlLastCell).Address).Select
        For Each Cell In Selection
            Application.StatusBar = ActiveSheet.Name & " Cell " & Cell.Address
            If Cell.HasFormula Then
                If InStr(UCase(Cell.Formula), UCase(Srch)) > 0 Then ' if the cell's formula contains the input string
                    wsThis.Range("A" & i).Value = ActiveSheet.Name
                    wsThis.Range("B" & i).Value = Cell.Address
                    wsThis.Range("C" & i).Value = "'" & Cell.Formula
                    i = i + 1
                End If
                If InStr(UCase(Cell.Value), UCase(Srch)) > 0 Then ' if the cell's value contains the input string
                    wsThis.Range("D" & k).Value = ActiveSheet.Name
                    wsThis.Range("E" & k).Value = Cell.Address
                    wsThis.Range("F" & k).Value = Cell.Value
                    k = k + 1
                End If
            End If
        Next ' next cell
    Next ' next sheet
    If i = 2 And k = 2 Then
        wsThis.Range("C3").Value = "No match found for " & Srch
    End If
    Application.ScreenUpdating = True
    Application.StatusBar = ""

End Sub
brett    19 Nov 2013, 07:31
thanx alot nice article
Rod Ritchie    27 Feb 2013, 16:37
I tried recording a new macro and inserting the above command stream in the comments but it was not effective. I don't know how to create a macro using that command stream any other way. I do however use a spreadsheet every day where I need to search 'workbook' rather than sheet and would love a resolution if I am able. Many thanks

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.