FIREMAGOS

View Original

AUTOMATIC SEARCH FILTERS IN EXCEL

In this EXCEL MAGO video, we show how to turn raw data into dynamic information that you can filter while you type. We recommend you to watch this video first, and check this article for more detailed information:

Now, let’s take each step at a time:

See this content in the original post

First, it’s very important to clean up your raw data since the very first step.

If there are additional rows, additional columns and/or text that you don’t need, you can clean it up at a starting point.

To ensure that we have the right file format, we can also choose a name for our EXCEL and save it as MACRO-ENABLED WORKBOOK. This step will be critical if we want, later on, to have our VBA codes up and running.

Step 2: Creating a dynamic table

What is a dynamic table?

Dynamic Tables in Excel are the tables where we add, or update, new values in an existing dataset. As a result, the table readjusts its size, changes the ranges into named ranges, autopopulates formulas based on the first row, refreshes or modifies the linked generated reports and Pivot Tables with the changes in the dataset.

How to create a dynamic table?

Dynamic Table name

Step 3: Activating the DEVELOPER MODE

CUSTOMIZE RIBBON

As soon as you are all set with your dynamic table, you can go to the next step, which is to activate the DEVELOPER MODE (unless you have previously activated it). And, there are 2 ways to do that.

The Simple way: is to right-click on any empty space in any MENU option (Home, Insert, …), and select CUSTOMIZE RIBBON.

(What we like to call) the Complicated way: Go to FILE>OPTIONS>CUSTOMIZE RIBBON.

Add the Developer Mode

Step 4: Developer TextBoxes

Hooray!!! You have your Developer Mode ON, and now you can do much, much more with your EXCEL files.

And, if you have the VIP-AREA password, you can also download lots of VBA CODES for FREE, and fully automate your EXCEL!

Now, let’s get back to our Developer TextBoxes.

What are ActiveX Control TextBoxes

TextBox is used to display data, edit data, etc in the rectangular box. The Textbox can be linked to a Worksheet Cell. The TextBox can have static and dynamic values. When data or text is static in the TextBox field, it represents the read-only information. It can be used on the WorkSheet or UserForm.

Setting up the text boxes

To set up an ActiveX Control TextBox, you can do in DEVELOPER>CONTROLS>INSERT>ACTIVEX CONTROLS>TEXT BOX.

As soon as you set up your ActiveX TextBox, you can create it as you wish. In our case is, again, a TextBox that works with a VBA code.

At this step, you can simply draw your TextBox in EXCEL. You can draw it anywhere in your Excel worksheet that makes sense for you.

TextBox properties

You can draw your TextBox as you wish, in the most relevant spot for you. Once you do this, you can right-click anywhere on the TextBox and select its PROPERTIES:

In our case, we need to pay attention to the TEXTBOX NAME and use either the default name or change it to something that we need to use in our VBA code later on. We also need to change the LINKED CELL value to a value from a cell where we want the data to mirror. For example, we can specify the G4 cell as linked cell.

With this data, we can imagine that we want to add a filter at the first column level, and search by YEAR.

Step 5: The VBA code(s)

Let’s look first at the full code, and then check it part by part:

Option Explicit

Private Sub TextBox1_Change()

Application.ScreenUpdating = False

ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:="*" & [G4] & "*", Operator:=xlFilterValues

Application.ScreenUpdating = True

End Sub

Sub Clear_All_Filters()

Dim lo As ListObject

Set lo = Sheet1.ListObjects(1)

lo.AutoFilter.ShowAllData

Call Clear_TextBox1_Data

End Sub

Sub Clear_TextBox1_Data()

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

If Application.WorksheetFunction.IsText(ws.Range("G4")) = True Then

ws.Range("G4") = ""

Else

End If

End Sub

THE SEARCH FILTER VBA

In the VBA (Visual Basic Application) editor, we can start by adding the first part of the code:

Option Explicit

Private Sub TextBox1_Change()

Application.ScreenUpdating = False

ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:="*" & [G4] & "*", Operator:=xlFilterValues

Application.ScreenUpdating = True

End Sub

What we need to ensure is that:

TextBox1: This should match exactly the name of the TextBox that we used before.

Table3: Is the name of the Dynamic Table that we specified.

Field:1: In the column number, or in which column, we want to search for our data. As we search by Year — which is the first column — we can specify field number 1.

G4: This is the cell that we linked to the TextBox (where we want the data to be mirrored).

Once we have all the details entered correctly, we can save and close the VBA editor and start using our search/filter bar to get the selection that we want.

Clearing the applied filters

As soon as we play with the start playing with the filters, we might feel the need to add another button to clear the previous filters once we are done.

There, after the first piece of code, we can add this part:

Sub Clear_All_Filters()

Dim lo As ListObject

Set lo = Sheet1.ListObjects(1)

lo.AutoFilter.ShowAllData

Clear_TextBox1_Data

End Sub

In this piece of code, what we actually state is that we want to clear all filters and deactivate the filters activated by our TEXTBOX.

Again, it’s very important to use the name of the TextBox; so, if your TextBox it’s not called TextBox1, then you need to use the name that you specified in the TEXTBOX PROPERTIES.

Please keep in mind that what we want to achieve is to create a CLEAR ALL FILTERS code that will be triggered by pressing on the SHAPE to clear all the active filters from the TEXTBOX.

As a final step, we might want to also clear the data in the TEXTBOXES because the clear filters is clearing the active filters, but it’s not clearing the text that we wrote for the search in the TEXTBOXES.

To do this, we can get back to our VBA editor, and add a new piece of code:

Sub Clear_TextBox1_Data()

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

If Application.WorksheetFunction.IsText(ws.Range("G4")) = True Then

ws.Range("G4") = ""

Else

End If

What is important in this code is:

  • We need to specify the correct Sheet name. In our case, it’s Sheet1.

  • The linked cell should be the one specified in the TEXTBOX properties. As you remember, we used G4.

  • As a condition, we can say that if G4 ISTEXT (it has text), we want to replace the text with nothing. In the EXCEL syntax nothing in a cell is represented by ““. If you add a space between the quotes, “ “, it will not work, as it won’t clean all the data, but it will leave one space that you would need to remove manually.

To make this last code work and triggered by the same shape, we need to change a bit the second code and at this line:

Call Clear_TextBox1_Data

The second code should look now like this:

Sub Clear_All_Filters()

Dim lo As ListObject

Set lo = Sheet1.ListObjects(1)

lo.AutoFilter.ShowAllData

Call Clear_TextBox1_Data

End Sub

So, now, when you press on the SHAPE it will trigger the second code, and the second code, before ending (before END SUB), it will trigger (CALL) the following code/the third one that will clear the previous search.

And you’re good to go and add even thousands of lines from where you can simply search by typing what you are looking for.

On EXCEL MAGO you have the video showing step by step what to do. On the VIP-AREA you have both the ready to use EXCEL FILE (FREE FOR DOWNLOAD), and the codes that you can download. Enjoy them all!


Related posts

See this gallery in the original post

Share this post on social media