Excel, Read the Find Field

[KenWK]

I have a macro that searches all the sheets in a workbook for the text entered into a dialog.

What I'd like to do is read the text from Excel's find field and put it in my dialog when its first displayed. In Word, for instance, Selection.Find.Text will give me what was last typed into the find dialog but I've not found an equivalent in Excel.

Is there a way read that?

Many thanks,
Ken

 

[SkipVought]

hi,

Generally like this...

dim rFound as range

set rFound = Cells.Find("What you want to find")

if not rFound is nothing then
   msgbox rFound.Value
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[KenWK]

Thanks Skip but that isn't exactally what I was looking for.

I basically want to read what's in the "Find what:" field of Excel's find dialog. So, if the user does a regular find in Excel then runs the macro, the macro's dialog will have the same thing they just searched in Excel.

Make sense?

Thanks again

 

[SkipVought]

No it does not make sense.

But if you want something like that to happen, then YOU, that is your total process, must present YOUR Find dialog, in order that YOU know what values have been entered by the user. The stock dialog is a black box.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[KenWK]

Yeah, a black, hairy box it wold seem.

I've added a "Match Whole Cell Only" check box to my dialog but when the macro finishes that option is turned on in Excel's built-in find dialog. So if the user then uses the regular find that option is on.

I guess this is kind of along the same lnies as reading the text from the find field of Excel's find dialog, but, is there a way to turn off that option without actually doing a search? So, basically, set the search options without executing a search. In Word, for instance, I can say "Selection.Find.MatchAllWordForms = False" and that optoin is turned off next time the find dialog is displayed.

Thanks again