Creating Smart Tags and Event-Code for AutoFilter Results

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

John R. Durant
Microsoft Corporation

June 2003

Applies to:
    Microsoft® Excel 2002
    Microsoft® Office Excel 2003

Summary: Learn how to use smart tags in an innovative way by making them respond to user filtering or sorting in worksheet. Create contextual smart tags so that, while the data remain constant, the existence of the smart tags depends on other activities and events in the spreadsheet. Learn how to write code that responds to auto filtering activities in the worksheet to create the subsequent smart tag context. (10 printed pages)

Contents

Business Scenario
AutoFilter
Developing an Event Mechanism for AutoFilter
Working with Smart Tags
Conclusion

Business Scenario

Before getting technical, it's always a good idea to take a step back and see how the code delivers business value. While the sample created in this article is not a full-featured business solution, it does form the basis for one. To understand the business scenario, think of a user who has a spreadsheet with hundreds of employees listed along with their most recent performance measurement, in this case some number. As can be expected, the spreadsheet user would most likely want to filter the results in the list to focus on select employees or just an employee. From there, the user would want to take some action. For example, the user can find the employees with the top three best measurements and then can take some action such as sending them a congratulatory e-mail or viewing detailed information about their performance.

This business scenario is supported by this article's sample using the AutoFilter feature and smart tag technology for Microsoft® Excel. The actions to take based on the employee's name and performance score are provided using smart tag actions. The AutoFilter feature is used so that only those employees who match the criteria are marked with smart tags. The benefit is that it does not make sense to mark up a large list of employees with the smart tag when only one or a few employees are to be selected.

AutoFilter

Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet the search criteria that a user or your code provides. Microsoft Excel provides two main methods for filtering ranges, AutoFilter for simple criteria, and Advanced Filter for more complex criteria. Unlike sorting, filtering does not rearrange a range of cells. Rather, filtering temporarily hides rows that do not match the search criteria. Once Excel filters rows, you can edit, format, chart, and print your range subset without rearranging or moving it.

When you use the AutoFilter command, arrows appear to the right of the column labels in the filtered range (see Figure 1). It is important to know that if you have more than 1000 unique rows in the range you are sorting, only the first 1000 unique entries appear in the drop down list (like the one in Figure 2) when you click on the column's arrow.

Figure 1. Apply a filter to cells to add arrows to column headings

Figure 2. Use a drop down list to choose which rows to make visible

You can cause a filter to apply to a range of steps.

To cause a filter to apply to a range of cells follow these steps:

1. Click a cell in the range you want to filter.
2. On the Data menu, point to Filter, and then click AutoFilter.

You use a custom AutoFilter to display rows that contain either one value or another. You can also use a custom AutoFilter to display rows that meet more than one condition for a column. For example, you might display rows that contain values within a specific range (such as a value of Fuller).

Filtering Ranges Programmatically

Although the sample in this article does not apply filters programmatically, it is likely that you could extend the sample to do so. In this light, it is useful to learn the basics of the AutoFilter object in Excel VBA. One of the most common ways to apply a filter programmatically to a range is to use the AutoFilter method. The general syntax using the AutoFilter method is as follows:

<expression>.AutoFilter(Field, Criteria1, _
   Operator, Criteria2, VisibleDropDown)

The method accepts five parameters all of which are optional. Omitting all parameters simply toggles the display of the drop-down arrows in the specified range. The following table lists parameters and their descriptions.

The following sample uses the method to filter a list starting in cell C4 on Sheet1 to display only the entries in which the field in the first column is equal to the string "Davolio". The drop-down arrow for first field is displayed.

Worksheets("Sheet1").Range("C4").AutoFilter _
  Field:=1, _
  Criteria1:="Davolio", _
  VisibleDropDown:=True

If you want to see what filters were applied to a worksheet, you can retrieve the AutoFilter property and access its properties. The properties are as follows:

Using the AutoFilter property let's you find out about filtered ranges and their settings in a worksheet. The following code loops through to find all filters and whether they are currently applied.

Dim oAutoFilter As AutoFilter
Dim oFilter As Filter
Set oAutoFilter = ActiveSheet.AutoFilter
For Each oFilter In oAutoFilter.Filters
  Debug.Print oFilter.On
Next oFilter

Developing an Event Mechanism for AutoFilter

One of the limiting aspects of using the AutoFilter feature in a business solution is that it has no directly associated event in the Excel object model. In other words, when a filter is applied or changed, by a user or programmatically, there is no explicit event for the activity. The limitation is that there are many situations when it would be useful to know that a list was filtered so that you can access the filtered results as a group for some other purpose. Similarly, it would be useful to know when the filter was removed, displaying the full list once again or if an existing filter was changed.

As explained in the business scenario, the sample in this article enables you to filter a list. You can then manipulate the visible row or rows for special purposes, after the filter is applied. In this case, the resulting rows are marked up with a smart tag. The smart tag has actions that you can apply from the Actions menu (see Figure 3), and these actions can use the text from displayed cell.

Figure 3. Mark up filtered results with smart tags

Looking over the process as a whole, there are three main things that you need to configure or code for the sample to work properly. First, you must create an event mechanism to make it possible to find out if activity in the workbook is the result of a user applying, changing, or removing a filter. Secondly, you must identify within code the cells that meet search criteria and those that do not. Third, based on which group the cells belong to, you must apply or remove either displayed (because they match the filter criteria) or hidden, smart tags.

To create an event mechanism it is necessary to force the worksheet to recalculate each time the filter criteria are changed. The target event is the Workbook_SheetCalculate event. This event fires whenever a user recalculates a worksheet or after a user plots any changed data on a chart. It is easy to force the recalculation by placing a function like NOW() in a cell anywhere on the worksheet (except in the range of filtered cells). As a result, any change made in the worksheet triggers the target event.

Working with AutoFilter Ranges

With this event firing, the next step is to distinguish what type of activity caused the event. For example, was the filter criteria removed or was a new one applied? This is important, because only the cells that are displayed when a filter is applied should be marked up with smart tags. The best way to do this is to set a Range object variable that, at first, represents the entire list of values that you can filter. Using the Workbook_Open event makes it possible to set the value before any other activities occur in the worksheet. The global declarations and event code look like this:

Private rngA As Range
Private lngLargeCount As Long
Private Const CUSTOM_SMART_TAG_TYPE As String = _
  "urn:schemas-microsoft-com:office:afnames#custom"
Private Sub Workbook_Open()
  Set rngA = GetRange()
  lngLargeCount = rngA.Count
End Sub 

The declarations include three variables, one for the range as just described, one to hold the number of rows in the range. You use this declaration in a procedure later in this article, and, rather than use the object reference repeatedly, it is convenient to use a simple variable. The third variable is a string constant containing the custom smart tag type that you use to mark up selected cells in the worksheet. There are specific smart tag actions associated with this type, defined in a smart tag DLL. By applying this type, the smart tag actions become available for each cell marked using this type. In this way, the smart tag DLL has no recognizer. If it did, it would be problematic in that the smart tag recognizer would recognize all of the employee names irrespective of whether they are part of a filtered list. For the current sample to deliver the desired end-user functionality, you must avoid this situation. Marking up the employee names as part of a context discerned in the VBA solution is a technique that you can use when wholesale recognition is not desirable.

The Workbook_Open event has code that calls a custom procedure, shown below, that returns a Range object filled with the visible cells given the current state of the worksheet's filter. As a user opens the workbook, because no filter is applied, the range contains all of the cells in the list.

Private Function GetRange() As Range
  Set GetRange = _
  Range("Sheet1!_FilterDatabase"). _
  SpecialCells(xlCellTypeVisible)
End Function

The most intriguing part of this code is that the reference to the Range object uses a named range, _FilterDatabase. This is a hidden named range that Excel creates in the workbook when AutoFilter is on. It refers to all of the cells in the list. By using the SpecialCells method with the xlCellTypeVisible constant, Excel only returns the visible cells. If the user makes no selection for the filter then all of the cells are visible and it returns the entire list. This procedure, GetRange, is called repeatedly so that when a filter is applied, a collection of only the visible cells can be retrieved.

The next step is to write code in the Workbook_SheetCalculate event, the event we use in response to activity in the worksheet. That code begins in the following way:

  Dim rngB As Range
  Dim rngTemp As Range
  Dim arrCellGroups() As String
  Dim i As Integer
  Dim j As Integer
  Set rngB = GetRange()
  If rngA.Address = rngB.Address Then
    Exit Sub
  End If

Use the first variable to get the collection of visible cells, given the state of the AutoFilter. To do this, call the GetRange method. Recall that rngA contains the range of cells before the Workbook_SheetCalculate event fired. If that range contains cells that are displayed as the result of a filter, then it contains cells marked up with smart tags, but the event could have fired for another reason such as input in an unrelated cell. The addresses of the two ranges are compared, rngA representing the "before" state of the range, and rngB representing the "after" state. If the addresses are the same then activity in the workbook is unrelated to AutoFilter, and the procedure is exited immediately. If the addresses do not match, then it is presumed that a change in the AutoFilter occurred.

Working with Smart Tags

Using the two range variables lets us compare the state of the overall list before and after the change to the AutoFilter. If the list expanded to include all values, it is necessary to remove all of the existing smart tags. Whatever the change in the AutoFilter, it is essential to remove any existing smart tags so that you can assess and mark up the currently visible cells. You can remove existing smart tags by using the following routine:

While rngA.SmartTags.Count > 0
 rngA.SmartTags(1).Delete
Wend

Because this routine only removes smart tags accessible from the cells in the specified range, other smart tags in the worksheet remain unaffected. From here, the code must determine if you need to add any smart tags. If the number of currently visible cells differs from the number of visible cells in the entire unfiltered list, then there must be some cells that require smart tag mark up. The following portion of the procedure finds those cells and adds smart tags to them.

If rngB.Count <> lngLargeCount Then
    arrCellGroups = Split(rngB.Address, ",")
    For i = 0 To UBound(arrCellGroups)
      Set rngTemp = Range(arrCellGroups(i))
      If rngTemp.Rows.Count > 1 Then
        For j = 1 To rngTemp.Rows.Count
          If rngTemp.Cells(j, 1).Row > rngB.Cells(1, 1).Row Then
          rngTemp.Cells(j, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)
          End If
        Next j
      Else
        rngTemp.Cells(1, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)
      End If
    Next i
  End If

Adding the smart tag to a cell is rather simple, but most of this code sorts through the address of the range of visible cells, determining which cells need the smart tag. One of the curious aspects of the range of visible cells after you apply a filter is how the address is returned. For example, look at Figure 4 and notice the row numbers in the visible cells.

Figure 4. Note the visible cells after setting an AutoFilter

The first row, number 4, contains the column headings. The second row is 5, the third 9, and the final one, 13. Only the first two are consecutive. When you retrieve the address of the visible cell range, it looks like this: $B$4:$C$5,$B$9:$C$9,$B$13:$C$13. There are three distinct groups of cells represented here. They are B4:C5, B9:C9, and B13:C13. As you can see, because the first two rows are consecutive they are in the same group. The other rows, not being consecutive, are in their own group. If row 12 did contain a value that matched the criteria, its cell reference is included with that of the last group, yielding B12:C13. The task is to separate these groups in order to identify one cell in each row and then add a smart tag to it.

The code does this by placing the entire address of the range in a string variable. Then, you can use the Split function to identify each group separated by a comma and to place that group in its own value within a string array like this:

    arrCellGroups = Split(rngB.Address, ",")

The code then loops through each element in the array and sets a new Range object's address equal to the array element's value.

    Set rngTemp = Range(arrCellGroups(i))

For ranges that contain only one row, like B9:C9 or B13:C13, adding the smart tag is done straightaway. You can add the smart tag to the cell in the first row, first column in this fashion:

    rngTemp.Cells(1, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)

For ranges that contain more than row, you must do a little more work. Consecutive rows pose a small challenge in that, in most cases, all that you must do is to loop through each row in the range and add the smart tag to the cell in the first column in the corresponding row. However, looping through each row could include the first row of the entire range (the one with the column headings) if the second row is visible. This is the case in Figure 4 where row 5 is included. You must avoid this first row by using the following loop with conditional logic:

    For j = 1 To rngTemp.Rows.Count
      If rngTemp.Cells(j, 1).Row > rngB.Cells(1, 1).Row Then
      rngTemp.Cells(j, 1).SmartTags.Add (CUSTOM_SMART_TAG_TYPE)
      End If
    Next j

The internal logic of the For . . . Next structure makes sure that the number of the row in question is not the same as that of the first row in the entire range. If the row number is greater, the smart tag is added. In all cases, the smart tag type specified in the constant described earlier is used.

The use of the smart tag presumes that you installed and property registered a smart tag DLL containing a class that uses the target type. You can use any smart tag actions by simply marking the cells up with the desired type. In this case, the target action class in the DLL can use the employee's name, retrieved from the cell, to collect information that is more detailed and present it to the user.

Conclusion

Even though there is no specific event relating to the AutoFilter feature, it is still possible to build solutions in response to AutoFilter activities. Filtering lists in a spreadsheet is a common task. The user can initiate this activity or you can do it programmatically in a more elaborate business solution. One of the most promising avenues is to use the custom event mechanism to find out which rows are visible after a filter is applied and then present the user with a list of actions they can take based on the data. Smart tags are an excellent way to give users lists of actions. They are unobtrusive and easy to engage. By simply adding a smart tag using a specific type, you can give the user contextual options for their filtered list results.