Search fields to my Excel spreadsheet.

New Contributor

Good day,

I would like to add a search field or several search fields to my Excel spreadsheet. The aim is that when I write a letter or a word in the search field that is automatically sorted and listed in the columns according to this search term. It should be a correct input field.

A colleague of mine once set it up, now he's no longer there and nobody knows how he did it. If necessary, I could show an example using a picture. But I also think it's clear what I mean.

Thank you!

1 Reply

@Herr_und_Meister 

Right-click the sheet tab of the worksheet with the search boxes.

Select 'View Code' from the context menu.

You'll see the VBA code that runs when you type in the search boxes.

It will look similar to this:

 

Private Sub TextBox1_Change()
    ' code here
End Sub

 

To create a search box yourself, switch back to Excel.

Make sure that the Developer tab of the ribbon is displayed.

If you don't see the Developer tab, select File > Options, then select Customize Ribbon, tick the check box for Developer in the list of Main Tabs on the right, and click OK.

In the Controls group of the Developer tab, click Insert > Text Box (ActiveX Control).

 

S0126.png

 

Click on the sheet where you want to insert the text box.

This will automatically turn on the Design Mode button.

Double-click the new text box to create the first and last line of the code for the text box.

You can then write the code, using the existing code as model.

Finally, switch back to Excel and click on Design Mode to turn it off.

If you later want to modify the text box (for example move or resize it), turn on Design Mode again.