'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value VbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!" MsgBox "The column heading was not found in cells " & DataRange.Rows(1).Address & ". 'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control Sht.Shapes("UserSearch"). = "" 'Control Form MyField = (ButtonName, DataRange.Rows(1), 0) 'mySearch = sht.Range("A1").Value 'Cell Inputįor Each myButton In ActiveSheet.OptionButtons 'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control MySearch = sht.Shapes("UserSearch"). 'Control Form 'Set DataRange = sht.ListObjects("Table1").Range 'Table Set DataRange = sht.Range("A4:E31") 'Cell Range 'Filtered Data Range (include column heading cells) 'PURPOSE: Filter Data on User-Determined Column & Text 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.
#CREATING A QUERY FOR MAC EXCEL 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 you want your search box to only filter on exactly what the user types, just remove the asterisks from the VBA code.
This means that you could search "whi" and the search would show any cell containing those 3 characters. The macro uses an open ended search (designated by the asterisk symbol before and after the search term). This macro will allow you to filter on any column with a text value within it. This is EXTREMELY important as the VBA code below will be filtering based on the text associated with the selected Option Button. Notice how all my example Option Button's have the exact same text as the headings in my data. You will, however, need to ensure that their text is verbatim with the data headings you will be filtering on. 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.įor your Option Buttons you will not need to change their object names (unless you really want to). Typically you will see a default name of "Text Box 1", however you can change this name to something more meaningful like "UserSearch". 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).
First you will need to determine the name of the text box that is holding your search term. The key to getting this code to work well is to setup your objects (aka form controls) properly. 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. I typically go the shortcut route as I like having the ability to place my search box wherever I want on my spreadsheet. Assign a keyboard shortcut to execute the macro, alleviating the need to click the Search button.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).There are two workarounds that I could think of: 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.
#CREATING A QUERY FOR MAC EXCEL PROFESSIONAL#
You can use the alignment tools to make everything look professional with even spacing. Your mouse should now look like cross hairs and you will just want to click somewhere on your spreadsheet to draw the Option ButtonĪfter you draw a couple of Option Buttons, you can drag them into place so that they are relatively close to your search box.Select the Option Button Form Control (first row, last icon).Click the Insert drop down button in the Controls group.Navigate to your Developer Tab in the Ribbon.To insert the Option Buttons you will need to 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.
0 kommentar(er)
