VBA for "Find" dialog box

[tmanger]

Hello all,

I have a large database of food items within an excel workbook that counts calories. I am looking to incorporate a search procedure, much like Excel already has under the Edit and Find (Ctrl+F) function, but using VBA, to allow users to easily look up their food items. I have tried recording a macro, but it turns up blank.

Is there a command to bring up the Find Dialog box? I don't want to modify anything in the database, simply search it, so any ideas are welcome.

Thanks in advance,

Tom.

 

[Tom Urtis]

You mean this?
Application.Dialogs(xlDialogFormulaFind).Show

As long as you are doing this with VBA, an input box works great in this situation too, and it looks more customized, but of course it's your decision.

 

[Smitty]

To my knowledge there's no xlDialog for Find, but you can use somethiung like this:

Sub FineMe()
    Cells.Find(What:=InputBox("Please enter your search criteria", "Search"), _
    After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
End Sub

Hope that helps,

Smitty

 

[Tom Urtis]

To my knowledge there's no xlDialog for Find

Yes there is, see my post above yours.
The one for Find and Replace (not just Find as per above) is
Application.Dialogs(xlDialogFormulaReplace).Show

 

[Smitty]

Thanks! (I've only ever used the Input Box method, so I looked in the wrong place...)

My bad!

Smitty

 

[NateO]

Other possibilities include,

Application.CommandBars.FindControl(ID:=1849).Execute

Application.CommandBars("Edit").Controls("Find...").Execute

 

[tmanger]

Excellent! Both will help.

Thanks again.

 

[Jst]

VBA for Fins Replace dialog

Thanks that is exactly what i have been searching for.
and saves a heap of special code

 

[Adamguk]

I am trying to build on the above code and merge it with an EntireRow.Copy code in order for the search to run through multiple (around 60) worksheets searching for any row containing the results of the InputBox value (text) and then copying/pasting the rows into a new worksheet by the name of "Search Results". Oh yh and the worksheets are all hidden.

Any ideas or example code please? ​

 

[RobHoffman]

Other possibilities include,

Application.CommandBars.FindControl(ID:=1849).Execute

Application.CommandBars("Edit").Controls("Find...").Execute

Great! Additional questions:

1. Where are the current "Find" values stored, so that one can save and restore them before using the above, and where is that documented?

2. Is there a way to pass arguments to the .Execute method to pre-fill the dialog before showing it? (if 1. is answered presumably one can just set the values before .Execute).

3. Are the names and types of the Find dialog arguments (or any other dialog) documented somewhere?

4. Are there figures somewhere that show the dialog identified by each of the values of the xlBuiltInDialog enumeration? (That way I'd know what xlDialogSearch is since

Application.Dialogs(xlDialogSearch).Show
​

doesn't do anything that I can see.

Thanks!