Real-time filtering in Excel


This is a follow-up question to an already-resolved thread located here:

In that thread, a VBA solution was provided which enables a single row of cells, located just above the column headings row of a table of data, to be used for two-step filtering. In other words, without the need to open any filtering boxes from the ribbon or quick access toolbar, a filtering criterion can be entered directly into a cell (1st step) and then by simply pressing {Enter} (2nd step), the filtering is done.

With that code now available, the purpose of this thread is to inquire about the feasibility of turning the above-mentioned two-step filtering process into a one-step process. In other words, is it possible to eliminate the 2nd step mentioned above (pressing {Enter}) so that real-time filtering occurs as you are entering a filtering criterion? This would be identical to the dynamic process which takes place when a drop-down auto-filtering box is open and the list of possible matches automatically adjusts in response to each new character entered into the search box.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Hi Steve,

Unfortunately, there is no worksheet event to handle a keystroke in a cell.  The code from the previous question uses the worksheet change event which fires after a cell is committed (i.e. when enter is pressed).  For something like that you would have to use another solution such as a VB/C# project using a datagridview that can handle many more events.
Martin LissOlder than dirtCommented:
Add this to your code which will send 'Enter'

SendKeys "~"
Unless I am not understanding correctly, he wants to filter in real time, so when "A" is pressed, it will filter A's, then a "D" is typed, it will filter by AD...

Basically like a SuggestAppend auto complete mode in a combobox as he mentioned.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

[ fanpages ]IT Services ConsultantCommented:

I read the question like you did FM.

I suppose text-box controls could be placed (exactly) over the filter criteria cells, with a single line as a border, a transparent back style, & a Z-Order "at the front" so that they look like worksheet cells.

Such a control will, of course, provide KeyDown, KeyUp, &/or KeyPress event handlers that code can be added within to support the functionality requested.


Martin LissOlder than dirtCommented:
I suppose text-box controls could be placed (exactly) over the filter criteria cells...
@Steve_Brady: The attached workbook illustrates an improvement on fanpages suggestion. It floats a single textbox over any cell in a range (in this case A2:F2). On exiting the cell the cell's text is set to the textbox's text, and while entering text in the textbox you can trigger your filtering from its Change event.

Here's the complete code:
Module1 Code

The grngCurrent variable is used to store the range of the cell your are in. If you run theShowForTesting macro you can then go into Design Mode and change whatever properties of the textbox that you like.
Option Explicit
Public grngCurrent As Range

Public Sub ShowForTesting()
  With ActiveSheet.txtFloat
    .Top = 10
    .Left = 10
    .Width = 50
    .Height = 50
    .Visible = True
    .Text = "This is txtFloat"
  End With

End Sub

Open in new window

Sheet Code
Option Explicit
' Highly modified code that was originally Developed by Contextures Inc.

Private Sub txtFloat_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Set grngCurrent = ActiveCell
    ' Move to next cell on Enter and Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim ws As Worksheet
Set ws = ActiveSheet

' Set the range where you want the textbox to appear
If Intersect(ActiveCell, Range("A2:F2")) Is Nothing Then
    txtFloat.Visible = False
    Exit Sub
End If

On Error Resume Next

If Not grngCurrent Is Nothing Then
    grngCurrent.Value = txtFloat.Text
End If
Set grngCurrent = ActiveCell

Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If

With txtFloat
    .ListFillRange = ""
    .LinkedCell = ""
    .SpecialEffect = fmSpecialEffectFlat
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width
    .Text = Target.Value
End With

  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

Open in new window


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
Steve_BradyAuthor Commented:
Many thanks.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
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.