Find button that highlights results

June 26, 2011 at 02:47:11
Specs: Windows 7
First of all I have no VBA experience, but I need to create a find button in Excel that will have the user type in a word or number to search within a spreadsheet of which multiple results will be highlighted for quick access. Then once the "search box" has been closed, the highlights will disappear for future searches. Currently I am being asked that it not utilize the "Conditions formatting". ANY help would be appreciated.


See More: Find button that highlights results

Report •


#1
June 26, 2011 at 16:14:46
You are aware that Excel's Find dialog includes a "Find all" option, aren't you?

When you use Find All, a link is provided to each found item so that the user can "quickly access" each item with the dialog box open.

It won't highlight them all, but it will take the user directly to each one as each link is clicked.

re: "Then once the "search box" has been closed, the highlights will disappear for future searches."

Highlighting the found items via an InputBox created within VBA will not allow the user to access the found items with the InputBox open.

VBA can be certainly be used to present the InputBox and highlight (or un-highlight) all of the found items, but once the user clicks OK to find/highlight the items, the Input Box will close.

For example, this code will present an InputBox to the user, close after the user clicks OK and then highlight all of the found items in Column A.

Once it is run again, it will un-highlight the previous found items and get ready to highlight the next set of items.

Sub ColorMyFinds()
lastRw = Range("A" & Rows.Count).End(xlUp).Row
 Range("A1:A" & lastRw).Interior.ColorIndex = xlNone
  get_Term = Application.InputBox("Enter Search Term")
    With Worksheets(1).Range("A1:A" & lastRw)
      Set c = .Find(get_Term, LookIn:=xlValues)
        If Not c Is Nothing Then
         firstAddress = c.Address
         Do
            c.Interior.ColorIndex = 6
            Set c = .FindNext(c)
         Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 27, 2011 at 02:16:43
Thank you for your assistance. Yes, I am aware of the "find all" option, however, the current need is to have a button on the spreadsheet itself.

Is it possible to have the find button AND have the multiple results highlighted while giving the user the ability to scroll through the sheet to see the highlighted items? And then once the user has found the desired result, close the input box and thereby deactivating the highlights.

Or another option is it have the "Find" button that generates a input box with a "Find Next" option highlighting each individual result each time the "find next" is clicked, thereby taking the user through the sheet to see each highlighted result. And then as above, once the user has found the desired result, close the input box to deactivate the highlights.


Report •

#3
June 27, 2011 at 06:18:01
There's no sense in reinventing the wheel since the built in Find/Replace feature already gives you what you asked for in your last paragraph, with the addition of Find All which provides links to the items found with - and it can be assigned to a button in the spreadsheet.

Granted, it doesn't highlight all of the found items, but if you are using Find Next or the links provided with Find All, I'm not sure that highlighting is all that important.

1 - Press Alt-F11 to open the VBA editor
2 - Click Insert...Module
3 - Paste the following code into the pane that opens
4 - Create a button on your worksheet, righttt-click it, choose Assign macro
5 - Assign the OpenFind macro to the button.

Sub OpenFind()
 Application.CommandBars("Edit").Controls("Find...").Execute
End Sub

To answer your speific question as to whether the InputBox can be open while the user scrolls through the spreadsheet, all I can say is that I don't know of a way to do that other than with the built in Find dialog box as described above.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question