Excel Macro Search Box

EE Pros,

I'm a advanced novice Excel user trying to run a substantial sales operation.  I have a worksheet that has approximately 5000 Customers so, 5000+ rows and approx. 30 columns of data.  Every time I have to look up a Customer, I am using the Filter to uncheck "All" and look down the list for the particular record.  This is nerve racking as it can take 30 seconds or more to look up a particular record in column A.  

What I'm looking for is a small piece of VBA Code that can assign a "key" such as "L" (for look up) and up pops a box that seeks the character string typed into the box focused on either a particular column, say "A" or has a option to "search all" which would be the entire sheet.

Can I get some help with this?  I think it's rather simple for someone who knows VBA.  I have the skills to integrate it into my worksheet...but simply need help with the lines of code, search algorithum and probably the form that needs to come up in order to perform the search (with link to the assigned key (such as Ctrl+L -- for Look Up).

Much thanks in advance,

B.
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Why not use Ctrl-F

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

Regards
0
duncanb7Commented:
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
0
Bright01Author Commented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

duncanb7Commented:
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
0
Bright01Author Commented:
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.
0
duncanb7Commented:
It is a good tuturial  at http://www.youtube.com/watch?v=KKJj78LoFaA

Duncan
0
Bright01Author Commented:
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
0
duncanb7Commented:
try a small and test example to start after viewing the video, work around it, you will get it


Duncan
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
Duncan,

Here's what I tried; no progress here.

Sub Macro1()
Application.OnKey "^{F}"
End Sub
0
duncanb7Commented:
Put the work_activate at the sheet of "ThisWorkbook" using  VBA editor
and the function will be activated automatcially once the file is re-open.Please also read the attachment for example code
Private Sub Workbook_Activate()
    Application.OnKey "+^{F}", "functionkey"
End Sub

Open in new window

Duncan
functionkey.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
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.
0
duncanb7Commented:
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

Open in new window

functionkey.xls
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
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.
0
duncanb7Commented:
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
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
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
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
Duncan,

Again, thanks for the help!

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.