Resetting Find

[kirkm]

I've found macros in my worksheet are setting Excels Find
to non default parameters.

I fixed this by the following as the workbook closes

Dim Rng As Range
With Range("A1")
Set Rng = .Find(What:="", after:=.Range("A1"), LookIn:=xlFormulas,
LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext,
MatchCase:=False, matchbyte:=False)
End With

It works but has a bad side effect. Column A uses
Private Sub Worksheet_SelectionChange to do stuff and this
is being activated each time it's run.

I suspect setting Range to something other than A1 (that does
nothing), it might fix it. But is that a good solution, or is a
completely different approach called for?

Thanks - Kirk

 

[CurlyDave]

I've found macros in my worksheet are setting Excels Find
to non default parameters.

I fixed this by the following as the workbook closes

Dim Rng As Range
With Range("A1")
Set Rng = .Find(What:="", after:=.Range("A1"), LookIn:=xlFormulas,
LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext,
MatchCase:=False, matchbyte:=False)
End With

It works but has a bad side effect. Column A uses
Private Sub Worksheet_SelectionChange to do stuff and this
is being activated each time it's run.

I suspect setting Range to something other than A1 (that does
nothing), it might fix it.  But is that a good solution, or is a
completely different approach called for?

Thanks - Kirk

What are you using the selection change for? Could something else be
used?

 

[Dave Peterson]

You could add a couple of lines of code:

Application.enableevents = false
'your find code
application.enableevents = true

But if the user does their own Find (changing some parms), then your code will
reset them, too.

(As a user, this loss of default options doesn't bother me.)

 

[kirkm]

What are you using the selection change for? Could something else be
used?

I'm not sure... it checks Target.Row And Target.Column
and uses Application.StatusBar to display info.

 

[kirkm]

You could add a couple of lines of code:

Application.enableevents = false
'your find code
application.enableevents = true

Thanks very much dave, thats seems to work
perfectly.

Cheers - Kirk

 

[Gord Dibben]

Point the code to a dummy worksheet that contains no event code.

With Sheets("Dummy").Range("A1")

The Find settings are saved with the workbook.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I don't think that the settings are saved with each workbook. I think they're
remembered by the application--and forgotten when a new Find is run or excel
closes.

 

[Gord Dibben]

Right again Dave.

Spurious testing..............why am I not surprised<g>

 

[Dave Peterson]

It can be an irritating feature for some.

Right again Dave.

Spurious testing..............why am I not surprised<g>