Excel Macro Search Box

Hi,

Why not use Ctrl-F

if only select Col A first, it will only lookinto the column A

Regards

There is all function key on Excel workbook,
http://office.microsoft.com/en-001/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx

Hope it will assist you to solve your issue or give you some hint

Duncan

Rgonzo and Duncan,

Very helpful!  Here's my question; can you guys show me how I can construct a simple "Button" that drives a macro that reflects using the "Function Keys" in Excel?  Since I have users using the Worksheet, I'd like to make this very simple.  If I can have a single Button that when pressed, it drives the specific Function, such as Alt. + F, then I don't have to train them on the shortcut.

Much thanks,

B.

There is three wayes to do this
1-on VBA editor, try to write your example function macro  called test()
On Excel 2007, insert shape such as circule, rect,...etc.
And right click the sharp and then select test() macro from the tab of "Assign Macro"

2-You can use "Macro Recoding" tab to recode all function(atl+F) you click on the Excel sheet and
then look into the VBA editor, the record macro  will save all your click, editing, draw, selecting action..etc on your Excel sheet  into the VBA macro  

3-take a look at onkey on VBA at http://msdn.microsoft.com/en-us/library/office/ff197461(v=office.15).aspx

Duncan

Duncan,

I know how to link/assign the macro.  I tried approach 2, by recording a macro, selecting CTRL+F, up came the find box, I stopped the Macro record.  And got nothing.  Can you give me some coaching here or show me an example?

Thank you,

B.

It is a good tuturial  at http://www.youtube.com/watch?v=KKJj78LoFaA

Duncan

Watched the tutorial.... but doesn't address a Control + F recording.   Here's what I'm doing:

1.) Selected Record Macro
2.) Selected Relative Referernce
3.) Control + F (to initiate the "find function")
4.) Stopped the Macro
5.) Viewed the Macro

I get Nothing in the Macro.

B

try a small and test example to start after viewing the video, work around it, you will get it


Duncan

I did!  It didn't work.  It doesn't get any smaller then two keystrokes.

Watched the tutorial.... but doesn't address a Control + F recording.   Here's what I'm doing:

 1.) Selected Record Macro
 2.) Selected Relative Referernce
 3.) Control + F (to initiate the "find function")
 4.) Stopped the Macro
 5.) Viewed the Macro

 I get Nothing in the Macro.

Duncan,

Here's what I tried; no progress here.

Sub Macro1()
Application.OnKey "^{F}"
End Sub

Duncan,

Works great!  I would have never been able to figure that out.... really appreciate the help.

One question; is there a way to keep it from having to be an exact match?  It works great but I have to use the wildcard to find a partial char. string.

Thanks,

B.

Using widcard with find is hard, I try to use Instr with find

Duncan

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
                    If InStr(Cell, FindString) <> 0 Then
                    GoTo Final
                    End If
                    Next Cell

Select allOpen in new window

functionkey.xls

Interesting on this pass.... if I try to find a record "above" where the cursor is in the worksheet, I get an error but it goes to the spot.

B.

Also, where you have "MatchCase:=False)", should that mean that it doesn't have to match cases?  Right now I have to put in exactly the Case for it to find a Name.  Example;  FedEx vs. fedex.

B.

Could you summarize what code you have and what last issue you have on new thread that
will be easier and faster  for other experts to look into it ?

Duncan

Yes.  Fair enough.   I've already put it in my production system and am making the changes as I get your recommendations.  Let me send you the code I have now in the Workbook.

B.

Module 1

Sub FunctionkeyFind()
Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter a Search Value")
    If Trim(FindString) <> "" Then
        With Sheets("PMQ_Sales_Pipeline").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
            For Each Cell In Range("B1", Range("B" & Rows.Count).End(xlUp))
            If InStr(Cell, FindString) <> 0 Then
            GoTo final
            End If
            Next Cell
                MsgBox "Nothing found"
            Exit Sub
            End If
            For Each Cell In Range("B1", Range("B" & Rows.Count).End(x1up))
            If InStr(Cell, FindString) <> 0 Then
            GoTo final
            End If
            Next Cell
        End With
    End If
final:
    Cell.Select
End Sub

This Workbook

Private Sub Workbook_Activate()
    Application.OnKey "+^{F}", "functionkey"
End Sub

Duncan,

How come we didn't just write a macro that brings up the Find Function as we originally had thought to do?  I couldn't get the macro to record the simple CTRL+F keystroke......

B.

I've requested that this question be closed as follows:

Accepted answer: 0 points for Bright01's comment #a40206172

for the following reason:

Duncan,

Thank you very much for the  work here.  I'm using it as a base and have learned new things thanks to you.  I left the other question open because Glenn also weighed in and I'm learning how to do the same thing a different way.

Again, much thanks and wishing you "my best"!

B.

Duncan,

Again, thanks for the help!

B.