Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Searching a Workbook by Default.

Searching a Workbook by Default

by Allen Wyatt
(last updated November 17, 2014)

19

Take a moment and display the Find tab of the Find and Replace dialog box. The easiest way to do this is to press Ctrl+F. When the dialog box is first displayed (See Figure 1.) 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. (You may need to click the Options button to see the Within drop-down list.)

Figure 1. The Find tab of the Find and Replace dialog box.

When you first display the dialog box, 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.

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 do another search, then the Within setting is persistent; it 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()
    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.

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. (Pretty ironic if you think about it—Excel no longer has menus, but you can still access the CommandBars collection to display dialog boxes using menus.) 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 (10348) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Searching a Workbook by Default.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing AutoComplete Words

AutoComplete allows you to easily complete words you are typing in your document. If AutoComplete is presenting you with the ...

Discover More

Complex Searches for Documents

When working with lots of documents, you may have need from time to time to discover which of those documents contain some ...

Discover More

Answering Questions in Order

It is not unusual to use Excel to gather the answers to users' questions. If you want your users to answer your questions in ...

Discover More

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!

More ExcelTips (ribbon)

A Fast Find-Next

Tired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do what ...

Discover More

Making All Occurrences Bold

Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.

Discover More

Superscripts in Find and Replace

The find and replace used in Excel is less powerful than its counterpart in Word, so it is not able to do some of the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 4?

2017-04-29 04:36:45

Alan Elston

@ Jimmy
These should get you started
http://www.eileenslounge.com/viewtopic.php?f=50&t=15555
https://excelribbon.tips.net/T008148_Understanding_Macros.html
https://excelribbon.tips.net/T005683_Recording_a_Macro.html
https://excelribbon.tips.net/T009753_Editing_Macros.html
Alan


2017-04-29 04:22:09

Alan Elston

Hi
A question was asked in the pre XL 2007 version of this article ( https://excel.tips.net/T003170_Searching_a_Workbook_by_Default.html )
I am rather curious myself as to the answer, considering that both articles are titled “Searching a Workbook by Default”.
‘_ - The code is great to set different parameters to the dialogue box. However I have not found a way to set the Within to workbook.
‘_- Can anyone tell me ( us ) how to do this with the code.
Thanks
Alan


2016-11-30 01:30:13

Jimmy

How to use this vb code in excel... A step by step guide would be appreciable.


2015-01-29 06:22:37

Mark

This works great for bringing the window up, but is there a way for a macro to automatically close this window down as well? I tried changing the .Execute to .Close but that failed. Any ideas?


2015-01-17 13:14:36

Pat B.

Kudos to Walter K for his suggestion. I've wanted to select within=workbook by default for years, now I have a method that makes that unimportant.

Highlight all the Tabs and then do a FIND? Right-button click, select all.

This method is BETTER than within=workbook because you can choose a custom set of tabs to search within.

Good thing, since the mistaken LookAt:=xlWhole is a non-starter. No range parameter can be specified.


2014-07-09 15:19:36

Jim Dawson

Like it! But is there a way to read the parameters so I can save them and then restore them after running my macro?


2014-06-11 09:41:25

Scott Renz

Thank you Walter K. I am trying it again. I have all the tabs selected. It still says "Sheet" in the "Within:" box. But yes, it is finding on all the sheets in spite of it saying "Sheet." I must have searched for something that was only contained in the first sheet on my previous try.


2014-06-10 13:00:21

Walter K

@Scott Renz
It looks like you may be only selecting the first Tab or Worksheet in the Workbook. Make sure you have all the Tabs selected that you want to search. To do this either hold down the Ctrl key for each Tab to search or select the first Tab then hold down the Shift key and select the final Tab in your Workbook. Should work, good luck!


2014-06-06 07:09:06

S Barker

@gerdami Many Thanks!


2014-06-05 11:52:31

gerdami

Just a warning about the persistent state of the Within box.

When you select a range and do a Find & Replace (F&R),
(1) if Within Sheet is selected, only the cells of the range are affected by the F&R
(2) if Within Workbook is selected, ALL Workbook cells are affected by the F&R



2014-06-05 11:13:45

gerdami

@S Barker:
The list of IDs is available at http://support.microsoft.com/kb/213552


2014-06-05 10:59:53

Scott Renz

Hi Walter,

It still shows "Sheet" for me when I select all the tabs and do Ctrl+F and it only searches on the first sheet.


2014-06-05 08:29:10

Bryan

@Walter. Wow, that may seem simple/obvious, but I never knew that functionality existed. Good suggestion.


2014-06-04 13:33:57

Walter K

Maybe most users or readers herein want a more simplisistic way to FIND in a workbook or you have 10-20+ Tabs or something. But instead of a Macro, isn't it just as easy to highlight all the Tabs and then do a FIND? Doesn't require you to change to Worksheet to Workbook or anything, just type in what you want to FIND or REPLACE and you're good to go. Am I missing something?


2014-06-04 10:07:05

Scott Renz

Hi Tony Davis,
xlWhole means the whole "Cell" must match the looked for text. Whereas, xlPart means only a part of the "Cell" can match the looked for text and it would be considered a match. xlWhole does not refer to the whole workbook. It refers to the whole cell.


2014-06-04 09:33:31

Dave Kerr

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 is 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
Sheets(j).Activate

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
Else
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
Range("A1").Select
Next ' next sheet

If i = 2 And k = 2 Then
wsThis.Activate
wsThis.Range("C3").Value = "No match found for " & Srch
End If

Application.ScreenUpdating = True
Application.StatusBar = ""
wsThis.Activate
Range("A1").Select
Columns("A:F").EntireColumn.AutoFit

End Sub


2014-06-04 08:16:09

Tony Davis

Al,

I'm not sure I understand your last sentence.
Are you saying you still don't know how to use the macro provided on the microsoft website to search the whole spreadsheet?
If so, today's tip tells you to set "LookAt" equal to "xlWhole" to look at the whole workbook.
I think you just need to specify xlWhole as the Lookat parameter.


2014-06-04 07:55:22

Al Verheggen

It is very useful to be able to replace the defaults of the search function. I connected Find2 with the ctrl+f shortcut and now can get customized default parameters. (I set the sort order to columns and the look in to values.) The valid parameters can be found at http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(v=office.14).ASPX

However, the parameters as posted do not look in the entire workbook, but instead require the entire cell to match the find string.

I would like to know how to set to search entire workbook, but the parameters as documented in the above address do not list a way to search the entire workbook.


2014-06-04 07:17:30

S Barker

So where are the IDs listed for each dialog/menu? And is there an equivalent list for Outlook and Word? I specifically would like the Outlook one, as one of the defaults drives me crazy.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.