Getting Excel Find to Default to Searching Workbook not Sheet

  • Thread starter Thread starter Tim Childs
  • Start date Start date
T

Tim Childs

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.
 
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}{tab}{tab}{tab}{tab}{tab}{tab}{enter}")
End Sub
 
Hi Ben

Thanks for getting back with a solution. Have incorporated it and it is
working fine

Am grateful for your help

Tim

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}{tab}{tab}{tab}{tab}{tab}{tab}{enter}")
End Sub
 
Tim,

I'm happy to help and glad to hear that it works for you. Thanks for the feedback.

Ben
 
Back
Top