13

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.

Note: Almost 10 years later, I'd still like to do this, but now for Excel 2016/2019.

7 Answers 7

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 "%(h){DOWN}{ENTER}{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.

Note: Old line from original answer: SendKeys "%(t)%(h)W~{ESC}"

2
  • 1
    I'd rather not use a macro for this. Oct 28, 2010 at 13:56
  • 2
    @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. Oct 28, 2010 at 18:38
1

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
0
  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

1
  • 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. Mar 7, 2012 at 19:13
0

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
0

I had a similar issue. I have button in a workbook. If the button is clicked I want excel to open the find dialogue with the option "search within all workbooks" already selected instead of the default "within sheet", no matter what.

Furthermore I wanted the button to work regardles off the options being hidden or shown and wether or not somebody made changes manually. This is tricky because Excel per default doesn't show the options on start but remembers and shows them again the next time if somebody displayed them before.

To make sure the keystrokes work we need a series of keystrokes that sets the setting regardless of the initial state. Took quite a bit of tinkering, but I found one that works.

Here's my code for the English version of Excel, for other languages you have to change the keys accordingly.

 Sub Commanbutton_Click()

      Cells(1, 1).Select

       SendKeys "^f", True
       SendKeys "{TAB 15}"
       SendKeys " ", True
       SendKeys "%t%t", True
       SendKeys "{TAB 2}", True
       SendKeys "{DOWN}{DOWN}{ENTER}", True
       SendKeys "%t%t", True

  End Sub

You can try it out manually:

Press CTRL+F for the find dialogue Press TAB 15 times so that you either land on "Options" or on the "Search within" dropdown.

Press space and either

1) Options are displayed or 2) the dropdown gets activated and nothing happens

Press Alt+T twice to reset the cursor to the find input box

Press TAB twice, it will take you to the search within dropdown, no matter what (because the previous steps ensure that the options are in fact displayed!)

Press DOWN arrow twice to select "Workbook" and ENTER to select

Press ALT+T twice to reset the cursor to the find input box again.

Hope this helps somebody else who has a similar problem. Lots of Googling only told me that it is not possible to preselect "search within all workbooks" with VBA but this way works!

To make it work with different languages you'd need to check for the language and use CASE to switch to the proper routine with the proper shortcuts.

-1

I created the following two macros and gave them shortcuts:

  1. This one sets the search to workbook:

     Sub SetSearchWorkBook()
         'Next Line would enable  match whole cell instead of search within cells as option
         'ActiveSheet.Cells.Find What:="", LookAt:=xlWhole
         Application.CommandBars("Worksheet Menu Bar").FindControl( _
           ID:=1849, recursive:=True).Execute
           SendKeys ("%t+%h+{DOWN}~")
     End Sub
    
  2. This one opens up the search box:

     Sub Search()
     Application.CommandBars("Worksheet Menu Bar").FindControl( _
           ID:=1849, recursive:=True).Execute          
     End Sub
    
1
  • Explaining how this is to be used and how it answers the question would make it much better as an answer. Right now it is a comment, information without context. Jun 25, 2020 at 3:20
-2

The issue is that the "Options" dialogue is a toggle. If you open it and don't close it, it will be open next time you open Find. That causes the tab counts to be off. I modified the macro so that I open Find, Tab 7 times to go to the Options tab and stop on the second hit not the first, hit space, hit ESC (which leaves the Options dialogue open), open Find again, tab twice with the Options dialogue already open to get to Within:, Arrow down twice to highlight Workbook, hit enter. For it to work properly I found reopening Find and using Alt T to put the cursor on the "Find What" line worked in the macro. The macro below works for me across all workbooks (saved to my personal.xlsb). It works even if the options dialogue is open or closed from previous use. I created a shortcut and added it to my quick access tool bar at the top of the workbook. I used the same icon as the find Icon. So to search across all sheets in the workbook I select the icon in my quick access toolbar (or ctrl-w). To search just in the sheet, or by the other options, I use the normal Find icon/function.

This one works for me:

SendKeys "^f", True
SendKeys "{TAB 7}"
SendKeys " ", True
SendKeys "{ESC}", True
SendKeys "^f", True
SendKeys "{TAB 2}", True
SendKeys "{DOWN}{DOWN}{ENTER}"
SendKeys "^f", True
SendKeys "%t", True

1
  • This is for Excel for Office 365, 32 bit.
    – Will Davis
    Jun 4, 2020 at 15:13

You must log in to answer this question.

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