Excel On Mac How To Insert Online Data Search For Format Data Point

Press the F1 key. Powerpoint for mac and degree symbol. Type Excel keyboard shortcuts in the search box. Click the link for Keyboard Shortcuts for Excel 2010. Right click on the topic heading then select Properties. Triple click the Address (URL) link then copy (Ctrl+C) to the clipboard. Click Cancel and close the Help window.

Why A Search Box? Ever since search engines came into fruition (I can still remember those ), search boxes have become an experience that everyone has become accustomed to. Can you think of the last time you have been on your computer and haven't interacted with some sort of search box or even an? It's unfortunate that Excel doesn't have a form control search box (maybe in the future?) as I could see that type of tool opening the doors to a ton of creative and time saving functionalities. But luckily you can create a search box on your own. In this post I will walk you through how to create a gorgeous-looking search box that can filter your data to only show your search results.

First I will show you how to set it up and then you will learn how to tweak the VBA code to fit your setup. Creating Your Search Box User Interface.

Instead of only allowing your users to filter on a single column, why not let them search through a few? By integrating Option Buttons with your search box you can have your users specify which column they want to search in.

To insert the Option Buttons you will need to • Navigate to your in the Ribbon • Click the Insert drop down button in the Controls group • Select the Option Button Form Control (first row, last icon) • Your mouse should now look like cross hairs and you will just want to click somewhere on your spreadsheet to draw the Option Button After you draw a couple of Option Buttons, you can drag them into place so that they are relatively close to your search box. You can use the alignment tools to make everything look professional with even spacing. One Pitfall The one pitfall that I could not seem to get around is that fact that after entering in your search text, you need to click outside of the textbox before you can click on the Search button. There are two workarounds that I could think of: • Instead of using a form control textbox, you can use either a Cell or ActiveX Textbox to hold the search text (I have lines of code in the below VBA macros commented out that can handle these situations) • Assign a keyboard shortcut to execute the macro, alleviating the need to click the Search button I typically go the shortcut route as I like having the ability to place my search box wherever I want on my spreadsheet. Also, ActiveX controls can sometimes be glitchy depending on which version of Office you are using, so beware if you end up using this route.

Now For The VBA! Naming Your Objects The key to getting this code to work well is to setup your objects (aka form controls) properly. First you will need to determine the name of the text box that is holding your search term.

To do this, you need to select the text box and then look at the Name Box (which is located to the left of the Formula Bar). Typically you will see a default name of 'Text Box 1', however you can change this name to something more meaningful like 'UserSearch'. Make sure you hit the Enter key immediately after typing in your new name to apply the name change! If you click outside of the Name Box before hitting enter, your text box will revert back to it's previous name. For your Option Buttons you will not need to change their object names (unless you really want to).

You will, however, need to ensure that their text is verbatim with the data headings you will be filtering on. Notice how all my example Option Button's have the exact same text as the headings in my data. This is EXTREMELY important as the VBA code below will be filtering based on the text associated with the selected Option Button.

Searching For Text Only This macro will allow you to filter on any column with a text value within it. The macro uses an open ended search (designated by the asterisk symbol before and after the search term). This means that you could search 'whi' and the search would show any cell containing those 3 characters.

If you want your search box to only filter on exactly what the user types, just remove the asterisks from the VBA code. To set up this code with your data you will need to designate your data range for the variable DataRange and you will also need to modify your text box name inside the Shapes reference.

If your data does not start in Column A you may need to add or subtract from the myField variable to ensure you are filtering on the correct column number associated with your data set. Linking Your VBA Code To The Search Button Once you have added your VBA code to your spreadsheet and are ready to see it in action, you will need to turn your search button into a trigger for your macro code. To do this, simply right-click on your button and select Assign Macro.