Set Excel to search within entire workbook by default

Question

In Microsoft Excel, when I search using CTRL+F, by default it only searches within the current worksheet.

How can I set it to search within the entire workbook by default? I have Office 2010. Is this even possible? Is it possible to set this for a specific workbook or worksheet? I'm okay with a registry edit if that's what it takes.

Answer 1

1. Select the Find/Select icon in the Home Ribbon

2. Click Find

3. Click the "options" box on the bottom right

4. On the left side it has a dropdown that reads, "Within"

5. Select "Sheet" or "Workbook"

This site explains how to change the default

Answer 2

I included a modification so it will work if your Excel is in Spanish (like mine)

Private Sub Workbook_Open()

Dim lCountryCode As Long
lCountryCode = Application.International(xlCountryCode)

Application.CommandBars.FindControl(ID:=1849).Execute

Select Case lCountryCode
Case 34 'spanish
    SendKeys "%(p)%(D)L~{ESC}"
Case Else 'default english
    SendKeys "%(t)%(h)W~{ESC}"
End Select


End Sub

Answer 3

You can do this with a Workbook_Open macro in the ThisWorkbook module, like so:

Private Sub Workbook_Open()

Application.CommandBars.FindControl(ID:=1849).Execute
SendKeys "%(t)%(h)W~{ESC}"

End Sub

This will set it for your session for the workbook you're in.

To have this always be the default you need to create a Personal Macro Workbook.