Here's how you can achieve the effect by utilizing the Personal.xls workbook. By following the 12 steps below, you can use Find to search for values with a Look in Values default instead of a Look in Formulas default, even if you used Formulas the last time you did a Find.
How to make Find default to Values instead of Formulas:
Close any open workbooks.
Create a new workbook.
Go to the VBE, and in the Project Explorer, click on VBAProject(Personal.xls).
Notes (in case anyone reading this does not know):
To get to the VBE, click on Tools > Macro > Visual Basic Editor, or press Alt+F11.
If the Project Explorer is not visible, while in the VBE click View > Project Explorer, or press Ctrl+R.
If you do not already have a Personal.xls project created, then create one, and return to Step 1. See Excel’s on line help for creating a Personal workbook.
While in VBE with the Personal.xls project selected, click Insert > Module, and paste this in:
Application.Dialogs(xlDialogFormulaFind).Show , 2
Click View > Microsoft Excel, or press Alt+F11.
Click on Tools > Customize > Commands tab.
With the Customize dialog box open, click on Edit from the worksheet menu bar. This will pull down the Edit menu options, and at the same time, it will enable the "Modify Selection" button on the Commands tab of the Customize dialog box.
Click "Find" on the File menu, then click the "Modify Selection" button on the Customize dialog box. In the Name field, you should see something like "&Find…". So far so good.
Click "Assign Macro…", then click "PERSONAL.XLS!FindValues", and click OK, then click Close.
Assuming you have the Standard toolbar visible on the sheet, click on the Find icon (it is a pair of binoculars), and repeat Step 8 and Step 9. One note, at this point, the Name field might say "Find" without the ampersand. That is normal.
Click the "Close" button on the Customize dialog box.
From the File menu, click Exit. If you are prompted to save changes to Personal.xls, choose Yes. If you are prompted to save changes for any other workbook (Book1.xls or whatever), choose No.
That works great, except now I can't use the normal Find command. What if I want CTRL-SHIFT-F to be "FindValues", and CTRL-F to be regular find? Is that possible? It seems like when I create the macro and assign it to CTRL-SHIFT-F, after I run it once, CTRL-F no longer does anything, nor does the Find buttons. It's like I can get either one or the other, but not both. Any ideas?
You asked for a way to circumvent the Excel default of Formulas as the Find argument, which was what I provided. Now it sounds like you need to circumvent the circumvention, to have Values appear all the time unless you employ a macro to have Formulas be the displayed argument some of the time.
Try this, untested, but place this macro in a standard VBA module, and assign it to a shortcut key with Ctrl+Shift+F or whatever you like. It will show Formulas as the argument:
.EnableEvents = False
.Dialogs(xlDialogFormulaFind).Show , 1
.EnableEvents = True
Right, I got how that will work. But why can't I have the regular Find and the FindValue Macro? The problem with the workaround is that you can edit the worksheet with the Regular Find window open, but not with the ones from the Macros.
I just want CTRL-SHIFT-F to open findValues, without it changing anything in the regular Find command. Why does the format change the latter?