Dim str Filter As String str Filter = "*" & [B5] & "*" Debug. As a result, the filter string is *M* and the filter returns any record that contains an M in the Name field, as shown in Figure K.
Now that you've seen the possible issues users might not have the expertise to work around, let's build that custom filtering control.In addition, it might help you in those instances when the built-in options aren't flexible enough. You can work with any simple data set or download the demonstration or file (which also contains the Data Validation find example from last month).We'll use the Table object, which isn't supported by the format, in the first solution.The second argument species the filtering string, which you supply by entering characters into the text box control. The example data is simple, but you can see how easily your users will adjust to using this custom filtering control.The Debug statement isn't necessary for the code to work, but I always include them when concatenating values for easy debugging. If you're still using Excel 2003 or you can't use a Table object, you'll need a dynamic name range.To do so, select the Table, click the contextual Design tab and enter a more meaningful name in the Table Name control, as shown in Figure G.Naming the Table isn't critical to this technique, but working with a meaningful name is easier.You convert the data set to a Table, put the controls in place, add a short VBA procedure, and users can delete records, modify existing records, and even add new records—and it all works.On the other hand, Excel's Table object has limitations.If its limitations won't impact use, use the Table solution. The first thing you need is a Table, so let's generate one from a simple data set as follows: Admittedly, the example is a bit contrived; the name values aren't autonomous (on purpose).In a perfect world, your data is always perfect, and your users know what to do with it.