Defaults of edit menus FIND

[josh]

When I use the Edit Menu's Find option I always have to change Search: By
Rows to Search: By Column and change Look in : Formulas to Look in :
Values. How can I change these defaults ?

 

[Dave Peterson]

I think that this is one of those things that excel will remember until you
change it (or you reopen excel).

You could add a dummy Find macro to a workbook that's always opened with
excel--maybe personal.xls???

Then have that macro do the find you want and close to get out of the way:

Or just create a new workbook and put this in a general module:

Option Explicit
Sub auto_open()
With Worksheets(1)
.Cells.Find What:="", After:=.Cells(1), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False
End With
ThisWorkbook.Close savechanges:=False
End Sub

(save the workbook before you test--that last line will close it and not save
the workbook!)

Name it DummyFind.xls, put it in you XLStart folder.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Robert Rosenberg]

You can pass arguments to the dialog to change its current choices. The
macro below brings up the built-in Excel Find dialog and changes the
"Search" and "Look in" choices:

Sub FindWithDiffDefaults()
Application.Dialogs(xlDialogFormulaFind).Show , 1, , 2

'Argument List (ones in CAPS are the ones used above):
' text, IN_NUM (Look in), at_num, BY_NUM (Search), dir_num,
match_case, match_byte
' The numbers used above represent the position of the desired choice in
the dropdown lists
End Sub

One large drawback to this method is that the old Find dialog is shown.
Excel 2002 (XP) and 2003 have newly designed Find and Replace dialogs that
allow for format conditions and multiple sheet searching. I haven't
researched it enough but at first glance I don't see updated/new Find and
Replace dialogs in the Application.Dialogs list.