Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

4 Answers

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
add comment

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
add comment
  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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.