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.

 

[RobHoffman]

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!

OK - found the answer to part of my question. http://msdn.microsoft.com/en-us/library/bb978780(v=office.12).aspx pointed me to Excel VBA Help "Built-In Dialog Box Argument Lists". So that partly answers 3. - the names of the arguments. In that topic, argument names for xlDialogFormulaReplace are given, for example. Still not obvious that this is the Replace dialog (one can experiment); still don't see in that list the Find and Replace dialog (although do so the Find dialog finally); still don't know the argument types. So I've got 0.5 of 4 questions answered. Thx.

 

[RoryA]

1. Nowhere accessible to VBA that I know of.
2. Yes, you can pass arguments.
3. The old macrofun.hlp file from MS is probably the best reference.
4. I've never seen a reference, but it's a nice idea.

 

[RobHoffman]

1. Nowhere accessible to VBA that I know of.
2. Yes, you can pass arguments.
3. The old macrofun.hlp file from MS is probably the best reference.
4. I've never seen a reference, but it's a nice idea.

1. Wow (print unfavorable opinion of MS VBA completeness).
2. OK -- I see how.
3. EXCELLENT. Found it, read warnings, downloaded it, using it!
4. I'll ask Mr. Balmer if he wants to pay me to do that.

Thanks.

 

[jessmall]

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

This is excellent! What if I want it to search the entire workbook instead of just the activated sheet?

Also, how can I get it to return "Not found" if the inputted value doesn't exist instead of getting a Debug Error?

 

[Ikester]

Other possibilities include,

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

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

I've found that both of these methods act exactly the same. Also there is a difference from the Application.Dialogs(xlDialogFormulaFind).Show method in that a script using the latter method doesn't complete until the dialog is closed, whereas the former brings up the dialog box and completes the script. Also the dialog box brings up the Find dialog box, (a dialog box I can't find in the list of commands available, at least not with Excel 2007) as opposed to the Find and Replace dialog box.

I'd really like to use the Find and Replace dialog box since it includes the ability to a) limit the search to the current sheet and b) it has the "Find All" capabilty, allowing me to preselect the search limits and with a single action, bring up the list of matches. However I'd also like to pass parameters to the dialog box. Does the Application.CommandBars approach support that?

 

[Ikester]

Also the dialog box brings up the Find dialog box, (a dialog box I can't find in the list of commands available, at least not with Excel 2007) as opposed to the Find and Replace dialog box.

Realized the above is unclear and/or misleading. Please substitute the following instead:

Also the latter method brings up the Find dialog box, (a dialog box I can't find in the list of commands available, at least not with Excel 2007) as opposed to the Find and Replace dialog box.

 

[scrizo]

Sorry for reviving the dead horse but this was the only code that actually did what I needed it to do, however is there a way to add to this for a Find Next, if another result might be found and not just return the first result

 

[CodeSquirrel]

Re: VBA for "Find" dialog box trapping error

Hi everyone
i have been using the code below which works fine until the item searched does not exist

so can so one tell me how i wrap an if statement or error detection
so that either a message box or it changes the sheet it is searching through

thanks

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

 

[scrizo]

Re: VBA for "Find" dialog box trapping error

There should be a check in there that looks like the following:

Public Sub FindText()'Run from standard module, like: Module1.


Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer


myText = InputBox("Enter client name to search EXAMPLE: wellgroup")


If myText = "" Then Exit Sub


For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)




[B]If Not Found Is Nothing Then[/B]
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address


Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select


myFind = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Your Result!")


If myFind = 2 Then Exit Sub


Set Found = .UsedRange.FindNext(Found)
Selection.Interior.ColorIndex = 6


Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With


Next ws


If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
[B]MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation[/B]
End If


End Sub

See if that helps you with what you are looking for

Hi everyone
i have been using the code below which works fine until the item searched does not exist

so can so one tell me how i wrap an if statement or error detection
so that either a message box or it changes the sheet it is searching through

thanks

 

[CodeSquirrel]

Re: VBA for "Find" dialog box trapping error

Thanks for your replay clearly i need do some more reading on the subject as there is a great deal in it that is over my head!

first a bit more information this workbook contains a number of sheet with book title on them arrange by isbn in column A , I have a button on the sheet that simply calls the sub when clicked

i am using a bar code scanner to input the data,

here is what happens the first scan finds the correct cell on the appropriate sheet and highlights the row green as required, the second scan however find the correct cell and highlights just the cell in yellow, the color is not terribly important and maybe a legacy of the original sheet color as that was in fact yellow i have since removed all color from the sheet (no fill) and it still does the same thing, also unlike the first scan where the search box pops up to allow the input of the data after the second scan no search box appears and only reappears if the button is clicked

regards pete

There should be a check in there that looks like the following:

Public Sub FindText()'Run from standard module, like: Module1.


Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer


myText = InputBox("Enter client name to search EXAMPLE: wellgroup")


If myText = "" Then Exit Sub


For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)




[B]If Not Found Is Nothing Then[/B]
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address


Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select


myFind = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Your Result!")


If myFind = 2 Then Exit Sub


Set Found = .UsedRange.FindNext(Found)
Selection.Interior.ColorIndex = 6


Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With


Next ws


If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
[B]MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation[/B]
End If


End Sub

See if that helps you with what you are looking for