Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer
    
I'd rather not use a macro for this. – Joel Coehoorn Oct 28 '10 at 13:56
1  
@Joel, the settings would most probably be stored in the .pip file for Excel, which is easy to find, but is in binary, and I'm unable to find a specific editor for it (that would be a great project). You could probably get a standard hex editor and work it out, but it would be a project. – Lance Roberts Oct 28 '10 at 18:38

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
share|improve this answer
  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

share|improve this answer
1  
This doesn't work, for two reasons. First, this option isn't saved with each document. Open a spreadsheet, make the change, save the spreadsheet, close Excel, re-open the spreadsheet, and it's back the way it was. Secondly, even if it did save with the document, I have to open a lot of documents that were not created on my machine, and therefore would have the (wrong) setting from the original machine where they were created. – Joel Coehoorn Mar 7 '12 at 19:13

Sendkeys commands as suggested by other answers are not encouraged. In the past Sendkeys command resulted in very unexpected actions, to where you may have to reboot computer to recover. I would recommend making the routine local and execute it on-demand only. For convenience you can also assign a cntl key to it like "q". The Workbook Within option will be remembered for balance of the session.

Sub myFind()
  Application.CommandBars.FindControl(ID:=1849).Execute
  SendKeys "%(t)%(h)W~+{Tab}+{Tab}"
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .