Resetting Find

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
 
C

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?
 
D

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.)
 
K

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.
 
K

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
 
G

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top