Search Item dialogue box irritant

[twaccess]

For a long time as long as I can remember I used to be able to pres
CTRL-F and type in any string and have excel search that portion o
string anywhere in my spreadsheets. Superb tool for navigating aroun
some of my larger spreadsheets...

BUT. lately I've found that the item in the dialogue box called "Fin
entire cells only" has come up ticked by default. This is a mino
irritant in that I have to remember to untick it before searching. Thi
was never the case previously so I must have inadvertently changed i
at some point.

Please could someone tell me how to have this unticked by default.

Thanks

Terr

 

[Andy B]

Hi

Which version of Excel do you have? I use 2000 and my dialog box remembers
the last setting I used.

Andy.

 

[twaccess]

Yup, mine is 2000 too !

Groan, looks like something more fundamental is wrong then.

Thanks

Terr

 

[Dave Peterson]

Maybe not.

Excel will remember the settings if you do the find via the user interface or if
a macro does the find.

You running any macros that may do a find?

 

[twaccess]

I use macros that do lookups etc.. but not find.

Excel does remember, but only until I shut the program down. When I
re-start, its there and ticked again.

Terry

 

[Dave Peterson]

I'm guessing that you have a workbook (or more) in your XLStart folder.

Maybe one of these have a .find command in it. If they set that switch, it'll
stay set.

And excel also starts addins in Tools|addins.

Maybe you could try an experiment.

Start excel in safe mode
windows start button|Run
Excel /safe
Ok

Safe mode means that excel won't load a lot of these extra tools.

Now try your ctrl-F. If that's still there, then I don't have an explanation.
But if it's gone, you've got more detective work.

I'd start by
1. Start excel
2. Uncheck all the options in Tools|Addins
(keep track of what you toggled)
3. Close excel
4. Move everything out of XLStart (use windows start button|Search/Find to
locate this), but keep track of the files you've moved.

Start up excel normally and test it out. Did it work ok? If yes, start adding
things back one at a time, closing and restarting excel each time and testing it
each time.

When it gets check, you've found the culprit. Then you have to decide whether
the addin/workbook is important enough to keep using or if it's possible to fix.

=====
If it turns out that it's important, but you can't change it (or get it
changed), then maybe you could add another workbook to your XLStart folder.

The purpose of this workbook would be to set up Find to be the way you want it.

Start a new workbook and record a macro that does a Find the way you want.

Then name that macro: Auto_Open
and right near the last line, add this: thisworkbook.close savechanges:=false

If the workbooks open in the right order, then this workbook will fix the
problem and then close.

Here's the format (but don't trust the settings!):

Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False

ThisWorkbook.Close savechanges:=False

End Sub

 

[Dave Peterson]

And I forgot to add: Don't forget to turn on all your tools|addins and move the
files back into XLStart when you're done.

 

[twaccess]

Peter

There looks a lot for me to try there, so it may be a day or so befor
I give it a go.

But, I wanted to just say a big thank you for the advice and I'll le
you know how I get on.

Suffice to say I have been dabbling a bit with add-ins lately so yo
have struck a bit of a chord there as its about that length of tim
since 'find started playing up

Thanks

Terr

 

[Dave Peterson]

Sounds like a theory close to my heart.

When did it break? Well, about the time I "fixed" it! <vbg>

 

[twaccess]

Groan...

It was a very useful but free utility that I've installed called ASAP.

Now this has run very well without problems until probably a few day
ago when I used a utility that selects cells according to whateve
value I choose.

I've deselected it from Addins and re-selected it, but it still has th
tickbox selected and the number "6" in the search field whic
presumably was the last string I was using.

Looks like I'll have to at least talk to those good people at ASAP an
see what they say.

Terr

 

[Dave Peterson]

Maybe you could just leave it off (unchecked) until you need it.

Then turn it on.