Resetting Find

  • Thread starter Thread starter kirkm
  • Start date Start date
K

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
 
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
Click to expand...

What are you using the selection change for? Could something else be
used?
 
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.)
 
What are you using the selection change for? Could something else be
used?
Click to expand...

I'm not sure... it checks Target.Row And Target.Column
and uses Application.StatusBar to display info.
 
You could add a couple of lines of code:

Application.enableevents = false
'your find code
application.enableevents = true
Click to expand...

Thanks very much dave, thats seems to work
perfectly.

Cheers - Kirk
 
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
 
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.
 
Back
Top