Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Macro Search Box

Posted on 2014-07-16
21
Medium Priority
?
2,398 Views
Last Modified: 2014-07-20
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.
0
Comment
Question by:Bright01
  • 13
  • 7
21 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40201286
Hi,

Why not use Ctrl-F

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

Regards
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40201322
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
 

Author Comment

by:Bright01
ID: 40204158
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Expert Comment

by:duncanb7
ID: 40204255
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
 

Author Comment

by:Bright01
ID: 40204259
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
 
LVL 13

Expert Comment

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

Duncan
0
 

Author Comment

by:Bright01
ID: 40204290
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40204294
try a small and test example to start after viewing the video, work around it, you will get it


Duncan
0
 

Author Comment

by:Bright01
ID: 40204315
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
 

Author Comment

by:Bright01
ID: 40206137
Duncan,

Here's what I tried; no progress here.

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

Accepted Solution

by:
duncanb7 earned 2000 total points
ID: 40206159
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
 

Author Comment

by:Bright01
ID: 40206172
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206210
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
 

Author Comment

by:Bright01
ID: 40206333
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
 

Author Comment

by:Bright01
ID: 40206335
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206336
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
 

Author Comment

by:Bright01
ID: 40206338
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
 

Author Comment

by:Bright01
ID: 40206351
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
 

Author Comment

by:Bright01
ID: 40206353
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
 

Author Comment

by:Bright01
ID: 40207320
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
 

Author Closing Comment

by:Bright01
ID: 40207321
Duncan,

Again, thanks for the help!

B.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question