Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel Find to Default to Searching Workbook not Sheet
hi
I wanted to write a small autorun procedure to make the Find option to start with assuming the whole workbook should be searched (rather than just the sheet). However the syntax for the VBA does not include that argument seemingly: Cells.Find(What:="national i", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Is there a workaround or alternative method? Many thanks Tim PS I know there is an excellent FlexFind utility but I don't load any add-ins unless I have to. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel Find to Default to Searching Workbook not Sheet
Tim,
The only workaround that I'm aware of outside of add-ins or complex find macros is to use SendKeys. It is not ideal, as this method is not very consistent. Still, using Excel 2010 with the following code in my ThisWorkbook module successfully set the Find option to Within Workbook. Note that if it does not work for you, you may need to note each keystroke that you use to perform the task and adjust the macro accordingly. Ben Private Sub Workbook_Open() Application.SendKeys ("^f{tab}{enter}{tab}{tab}{down}{down}{tab}{tab}{t ab}{tab}{tab}{tab}{tab}{tab}{enter}") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel Find to Default to Searching Workbook not Sheet
Hi Ben
Thanks for getting back with a solution. Have incorporated it and it is working fine Am grateful for your help Tim "Ben McClave" wrote in message ... Tim, The only workaround that I'm aware of outside of add-ins or complex find macros is to use SendKeys. It is not ideal, as this method is not very consistent. Still, using Excel 2010 with the following code in my ThisWorkbook module successfully set the Find option to Within Workbook. Note that if it does not work for you, you may need to note each keystroke that you use to perform the task and adjust the macro accordingly. Ben Private Sub Workbook_Open() Application.SendKeys ("^f{tab}{enter}{tab}{tab}{down}{down}{tab}{tab}{t ab}{tab}{tab}{tab}{tab}{tab}{enter}") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel Find to Default to Searching Workbook not Sheet
Tim,
I'm happy to help and glad to hear that it works for you. Thanks for the feedback. Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set the Find and Replace, Options, 'Within' default to Workbook | Excel Discussion (Misc queries) | |||
how do I set up preferences for a workbook/sheet by default? | Excel Discussion (Misc queries) | |||
How do I make the default FindWithinWorkbook in Excel? | Excel Discussion (Misc queries) | |||
changing the default type of searching in the find function | Excel Discussion (Misc queries) | |||
EXCEL: Find data in worksheet and create a new sheet in same workbook | Excel Programming |