Question about "Find" function in Edit menu

[cpayneHTE]

is there any way to default to the "workbook" setting instead of "sheet" when
using the find function?

 

[Dave Peterson]

I don't think so.

is there any way to default to the "workbook" setting instead of "sheet" when
using the find function?

 

[Bob I]

the setting "sticks" as long as Excel remains open, so perhaps run a macro?

 

[Dave Peterson]

That setting can't be changed via "normal" excel code.

Maybe using Sendkeys????

 

[Bob I]

Looks like the ticket.

That setting can't be changed via "normal" excel code.

Maybe using Sendkeys????

 

[Dave Peterson]

Sendkeys is something I wouldn't use if I were sharing the macro with others.

Too many things can go wrong.

If I have to use it for my own personal macros, then I won't be upset when one
of those many things go wrong.

 

[Peter T]

Not sure I understand the objective but if it's to reset the options in the
Find dialog to their default settings this macro should do it

Sub ResetFind()
Dim rng As Range

On Error Resume Next
Set rng = Cells.Find(What:="", _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
LookAt:=xlPart, _
MatchCase:=False)

End Sub

Regards,
Peter T

 

[Dave Peterson]

In xl2002+ (I think), there's an additional "within" setting under the Options
button to look in the entire workbook.

That setting isn't exposed through VBA.

 

[Bob I]

It's also in 2003, but you can't address it that I can see.

 

[Peter T]

Ah ha - that also explains why I didn't understand the objective !

Regards,
Peter T