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

On Feb 6, 6:25*pm, kirkm wrote:
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?

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 wrote:

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

--

Dave Peterson

On Fri, 6 Feb 2009 18:17:19 -0800 (PST), CurlyDave
wrote:


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.

On Fri, 06 Feb 2009 20:35:47 -0600, Dave Peterson
wrote:

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

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

On Sat, 07 Feb 2009 14:25:47 +1300, kirkm wrote:


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 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 wrote:

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

On Sat, 07 Feb 2009 14:25:47 +1300, kirkm wrote:


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


--

Dave Peterson

Right again Dave.

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




On Sat, 07 Feb 2009 13:25:23 -0600, Dave Peterson
wrote:

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 wrote:

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

On Sat, 07 Feb 2009 14:25:47 +1300, kirkm wrote:


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

It can be an irritating feature for some.



Gord Dibben wrote:

Right again Dave.

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

On Sat, 07 Feb 2009 13:25:23 -0600, Dave Peterson
wrote:

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 wrote:

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

On Sat, 07 Feb 2009 14:25:47 +1300, kirkm wrote:


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


--

Dave Peterson