Can you embed search function field on workbook sheets

Taupo58

New Member
Joined
Feb 23, 2015
Messages
43
With all the searching I've done it would appear that you cannot embed a search function into a field on a workbook sheet;but it's more than possible I haven't looked in every place???

I'm working with Excel 2016 Pro. While I appreciate it's not rocket science to use Ctrl-F and many may consider this an unnecessary wish list item, the answer may well be Excel can't do it anyway. But I needed to ask!!

The environment where this would come into paly is with a couple of Excel registers we have; one being our membership list which has in excess of 25K members - one to a row. In an ideal world, I would like to have a field at the top of the register with 'enter search query' and next to that a 'Search' button. The user would enter the membership No, for example, and the Search button would act as a trigger (not unlike a hyperlink button) which, for all intents and purchases, does exactly what Ctrl-F does but the user avoids having to open a separate function.

Cheers
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
If I've understood correctly, that is quite doable.

I'd set up the sheet like this

If necessary, move the headers & data down so that headers are in row 3.
Select row 4 and Freeze Panes (on the View ribbon tab). This ensures rows 1:3 are always visible.
Cell A1 will be your 'Search' box
Now..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering a membership number into cell A1 (no need to click a button, the search will just happen).
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm) & you will need to enable macros.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rFound As Range
  
  If Target.Address = "$A$1" Then
    Set rFound = Range("A3", Range("A" & Rows.Count).End(xlUp)).Find(What:=Target.Value, After:=Range("A3"), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If rFound Is Nothing Then
      If Len(Target.Value) > 0 Then MsgBox "Not Found"
    Else
      Application.Goto Reference:=rFound, Scroll:=True
    End If
  End If
End Sub
 
Last edited:
Upvote 0
Forgot to mention:
If the memberships are not in column A, but, for example, are in column J, then you could make the search box as cell J1 and the code might be better as follows.
In this code if the search box is used, the code re-selects the search box so it would be ready for you to enter another membership number if you want.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rFound As Range
  
  If Target.Address = "$J$1" Then
    Set rFound = Range("J3", Range("J" & Rows.Count).End(xlUp)).Find(What:=Target.Value, After:=Range("J3"), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If rFound Is Nothing Then
      If Len(Target.Value) > 0 Then MsgBox "Not Found"
    Else
      ActiveWindow.ScrollRow = rFound.Row
      Target.Select
    End If
  End If
End Sub
 
Upvote 0
Hey Peter

Your blood is worth bottling mate that does exactly what I want brilliant thank you so much!!!! Must say it is so frustrating that i just don't do anywhere the near the level of code to allow some of the syntax logic to sink into my brain but hey when there are guys like you out there that come up with it in barely minutes i can still live in hope Lol thanks again Peter.
Cheers
Brett
 
Upvote 0
Glad it worked well for you. Thanks for the kind comments. :)
 
Upvote 0
Hi Peter

Having now 'played' with this for a couple of hours one thing I have picked up on is it would appear the code can only be used on one column on the same worksheet? I found this out by thinking I was very clever (always a big mistake) and tried applying it to the next column i.e. column A being member No and Column B being member surname. What I did was copy the code for column A; then right clicked the sheet name and selected View Code; then pasted and altered the column A references to B and saved but this obviously caused an error whereby when I tried searching in the column A or B search fields I got a compile error saying "Ambiguous name detected: Worksheet_Change.
On the basis of the error I presumed it was because the code is singular or Excel will only allow one serch code of this nature per worksheet?
Was wondering what your take would be i.e. can you have more than one and if so what would need to alter in the code to allow a search for column A and another for column B?
Cheers thank you in advance for any advice you might be able to offer Peter.

Brett
 
Upvote 0
You can only have one Worksheet_Change code per worksheet.

If it is only columns A & B you want this for, try replacing the previous code with the code below.
If you intend to have these 'Search' boxed in row 1 of all, or many, columns then a slightly different approach may be needed. In that case, tell me what columns, or how the code would decide what columns were search columns.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rFound As Range
  
  If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
    Set rFound = Range(Target.Offset(2), Cells(Rows.Count, Target.Column).End(xlUp)) _
      .Find(What:=Target.Value, After:=Target.Offset(2), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If rFound Is Nothing Then
      If Len(Target.Value) > 0 Then MsgBox "Not Found"
    Else
      ActiveWindow.ScrollRow = rFound.Row
      Target.Select
    End If
  End If
End Sub
 
Upvote 0
IMorning Peter

Thanks once again for your prompt response; I removed the previous script and replaced it with the test one you provided last night and amended the ranges but, unless i did something wrong, neither worked.

As per your question, in the Member worksheet column B has the header STUDENT ID at B3 and column C has the header SURNAME at C3. I have used B4 & C4 as the two search cells i.e. where the user enters the search criteria, and B5 & C5 hold the first Student ID and Surname respectively. I have frozen Row 5.

These are the only two columns I would like to have the individual search cells for. However; in hindsight, a Surname search will in many instances have multiple results and could be too difficult to script a 'Next' function into the search i.e. I presume the cell search script would return the first smith or jones it finds in the column only? That being the case and I imagine scripting a 'Next' functionality would be prohibitive and therefore would be one of those cases of using the standard Ctrl-F, what do you think?

Cheers
Brett
 
Upvote 0
See if using AutoFilter suits better. Replace previous code with this. To show all data, just delete the entry from the search box.

If you modify the code and it does not work, please post the modified code and also describe in what way the code did not work. Not showing code and/or just saying code doesn't work gives us nothing to go on to diagnose the problem.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sTemp As String
  
  If Target.Address = "$B$4" Or Target.Address = "$C$4" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    sTemp = Target.Value
    Range("B4:C4").ClearContents
    Target.Value = sTemp
    ActiveSheet.AutoFilterMode = False
    If Len(Target.Value) > 0 Then Range(Target, Cells(Rows.Count, Target.Column)).AutoFilter Field:=1, Criteria1:=sTemp
    Target.Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top