'Live Lookup'/Filter

[rjbinney]

This is hard to describe what I'd like to accomplish...

Basically, I'd love a text box at the top of a column. As I type in the text box, the column automatically filters what's displayed.

Almost exactly like using the Search box IN the filter dropdown, but without the checkboxes.

Does that make sense?

Is that ridiculously lazy?

Thanks

 

[SpillerBD]

As I type in the text box....

Until you hit the ENTER key, the value of the cell hasn't changed for a filter (in Excel's normal world) to act on.
I don't remember ever seeing that implemented in an Excel workbook.

Is that ridiculously lazy?

Yes.
I work very hard to be lazy, otherwise I wouldn't bother with VBA. Others just call it being efficient.

 

[Peter_SSs]

I think that you can achieve what you want, provided you are open to using a macro-enabled file (*.xlsm). Try this as a "proof of concept".

In a fresh worksheet ..
1. Set up a heading in Cell A2 and a list of text below. Perhaps even use this list.

Excel 2016
	A
1
2	Hdr1
3	Tom
4	Bains
5	Tommy Smith
6	Jon kenny
7	Waits
8	Jonathan Kearns
9	Ted
10	Joe Rainsford
Filter as you type

2. On the Developer ribbon tab, click the drop-down under 'Insert' in the Controls section and from the ActiveX Controls section choose Text Box (ab| symbol) and draw a Text Box over cell A1

3. Right click the Text Box & choose Properties. Set the LinkedCell to A1. Close the Properties window and click Design Mode in the Developer ribbon to exit Design Mode.

4. In cell B1, enter the formula =A1 (Later, if this works you might want to format B1 and/or A1 to white text so you don't see the changes there)

5. Right-click the sheet's name tab and choose "View Code".

6. Copy & Paste the code below into the main right hand pane that opens at step 5.

7. Close the Visual Basic window & test by typing into the Text Box. For example, if you type ai you should see the list above reduce to 3 names. Adding an n the list should reduce to 2 names. Clearing the text box should cause all names to reappear.

Private Sub Worksheet_Calculate()
  Static sLast As String
  Dim sCurr As String
  
  sCurr = "*" & Range("B1").Text & "*"
  If sCurr <> sLast Then
    Range("A2:A100").AutoFilter Field:=1, Criteria1:=sCurr
    sLast = sCurr
  End If
End Sub

 

[rjbinney]

I know it's lazy, but the question is: ridiculously lazy?!?

Can't wait to try that out, Peter - yes, it's already an xlsm file, so learning a little more VBasic can't hurt. Thanks!

 

[Peter_SSs]

Can't wait to try that out, Peter

Actually, I over-complicated my suggestion. Instead, I suggest that you try the following.
Still so steps 1 & 2 as per my previous post then

3. Right click the Text Box but this time choose "View Code". That should open the vba window and show something like

Private Sub TextBox1_Change()

End Sub

4. Copy the code below & paste between those two lines shown above

Static sLast As String
Dim sCurr As String

sCurr = "*" & TextBox1.Text & "*"
If sCurr <> sLast Then
  Range("A2:A100").AutoFilter Field:=1, Criteria1:=sCurr
  sLast = sCurr
End If

5. Close the Visual Basic window & test by typing into the Text Box. For example, if you type ai you should see the list above reduce to 3 names. Adding an n the list should reduce to 2 names. Clearing the text box should cause all names to reappear.

 

[rjbinney]

Oh that is effin' brilliant.

One thing that's weird - it's pulling in a value from ANYWHERE in the table.

That is, if what I type is in another column, it's still catching it. I can't duplicate it in your table example, but in my big honkin' list... But you know what? It's actually MORE helpful that way.

Thanks

 

[Peter_SSs]

Oh that is effin' brilliant.

Cheers. Glad it is working well for you.

One thing that's weird - it's pulling in a value from ANYWHERE in the table.

That is, if what I type is in another column, it's still catching it. I can't duplicate it in your table example, but in my big honkin' list... But you know what? It's actually MORE helpful that way.

That does not sound right to me and I certainly would not rely on it to 'catch' all instances in other columns. I can't imaging what circumstances would cause such behaviour, but then I don't have your actual workbook to try to work out what is happening.