web 2.0


VSTO 2008: Custom Smart Tags in Excel 2007

First of all, what is a Smart Tag? “Smart Tags” is a feature of Microsoft Office that recognizes certain kind of text (such as a date) and then offer the users a list of possible actions related to that text.

Excel already comes with a plethora of Smart Tags, but we can also implement our own list of Smart Tags. When Smart Tag is active, when you hover the mouse cursor over the word a little button is displayed. Clicking on it will open the Smart Tag Actions.

You can turn on/off Smart Tags at:


1.   
Office Button
2.    Excel Options
3.    Proofing à AutoCorrect Options
4.    Smart Tags


The image below shows the Smart Tag in action when Microsoft’s financial symbol is typed in a cell:


Figure 1: Smart Tag at work

Let us start by creating a new Excel project as follows:
 

1.    File à New Project
2.    Project Types 
à Office 2007
3.    Excel 2007 Workbook (or Add-in, if you prefer an Excel Add-in)

The first thing we will do is to check whether Smart Tags are active in Excel. You should add the following code to the workbook’s Startup event (notice that below I also add the event for the click on the Smart Tag button we will create later. The code must come after Thisworkbook class):

    WithEvents MySmartTagClickAction As _
                       Microsoft.Office.Tools.Excel.Action



    Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles Me.Startup
        If Application.AutoCorrect.DisplayAutoCorrectOptions = False Then
            Application.AutoCorrect.DisplayAutoCorrectOptions = True
            Me.SmartTagOptions.DisplaySmartTags = _
                Excel.XlSmartTagDisplayMode.xlIndicatorAndButton
            MsgBox("Smart Tags have been enabled...", _
                   MsgBoxStyle.Information, "Smart Tags Enabled...")
        End If
        BuildSmartTags()
    End Sub

When the code is run, if Smart Tags are disabled, they will be enabled and a message will be shown:


Figure 2: Enabling Smart Tags via code

Now create a Smart Tag list in one of your worksheets. You can later change its visibility and hide it away from the user (Home Tab
à Cells à Format à Hide & Unhide à Hide Sheet):


Figure 3: Smart Tags list

As you will remember from the Startup code, we had a call to a procedure named
BuildSmartTags. This is our next objective:

    Sub BuildSmartTags()
        'Smart Tag object. Define the namespace and the identifier.
        'Here I use the namespace as my web address and identifier as
        'My Smart Tag
        Dim oMySmartTags As New  _
            Microsoft.Office.Tools.Excel.SmartTag( _
            "www.msofficegurus.com/MySmartTags#SmartTags", _
            "My Smart Tag")

        'Worksheet object that holds the list of Smart Tags
        Dim oSmartTagListWorksheet As Excel.Worksheet

        'Counter to loop through Smart Tag list
        Dim lngCount As Long

        'Smart Tag string stored in the worksheet
        'to be added as the Smart Tag Term

        Dim strSmartTagTerm As String

        'Set initial counter equal to 1
        lngCount = 1

        'Set worksheet that contains the Smart Tag list
        oSmartTagListWorksheet = Me.Sheets("Smart Tags List")

        'Set the first Smart Tag term
        strSmartTagTerm = _
            oSmartTagListWorksheet.Range("A" & lngCount).Value

        'Loop through Smart Tag Term List
        Do Until Len(strSmartTagTerm) = 0
            oMySmartTags.Terms.Add(strSmartTagTerm)
            lngCount = lngCount + 1
            strSmartTagTerm = _
              oSmartTagListWorksheet.Range("A" & lngCount).Value
        Loop

        'New Action button to be added to Smart Tag Action list
        'The name in parentheses is the name of the button
        MySmartTagClickAction = New  _
            Microsoft.Office.Tools.Excel.Action( _
            "Go to MS Office Gurus online")

        'Associate the button above with the
        'Smart Tag object (oMySmartTags)

        'that we declared above
        oMySmartTags.Actions = New  _
            Microsoft.Office.Tools.Excel.Action() _
               {MySmartTagClickAction}

        'Add the Smart Tag object (oMySmartTags)
        'the VstoSmartTags collection
        Me.VstoSmartTags.Add(oMySmartTags)
    End Sub

The final piece of the jigsaw is the code to handle the click event:

    'Event that handles the action click
    Private Sub MySmartTagClickAction_Click( _
        ByVal sender As Object, _
        ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
        Handles MySmartTagClickAction.Click

        Me.FollowHyperlink("http://www.msofficegurus.com", _
           , True, True)
    End Sub


You can now try the code out and see who it works as you type any word from your list:


Figure 4: Custom Smart Tag in action

Tags: , ,

Microsoft Excel | VSTO - Excel

Comments

Suzanne Brazil, on 4/22/2009 6:35:46 PM Said:

Suzanne

Hi there,

First of all, thanks for the post! Very nice! However, I have not managed to replicate it.

For example, if I enter "My Name" as the Smart Tag term, Excel does not recognize it. What is going on?

Robert Martim Brazil, on 4/22/2009 7:13:28 PM Said:

Robert Martim

Hi Suzanna,

Thanks for the comment. Unfortunately, you cannot add a sentence. You can only add one word. However, there is a solution to it, but it requires some changes in the code.

I am unable todo that now, but I shall post something to that effect.

Thanks
Robert

Mark M United Kingdom, on 9/2/2009 5:02:18 AM Said:

Mark M

Hi

Given your exerience with excel smart tags I was just checking if you (or anyone else on here) had at any point run in to a problem with the excel proofing > smart tags checkbox list.

It appears that visually they are displayed with custom tags at the top of the list, but are handled behind the scenes as if the had been databound in alphabetical order (as word does it), leading to major problems attempting to enable or disable individual tags. This can be proven by using the standard MSDN example code for smart tags (final exmaple at msdn.microsoft.com/en-us/library/ms178788.aspx). If you set the name to be alpabetically before the first standard tag everything is fine, but set it so its after and everything goes screwy.

I have reported this to MS, as well as posting in a number of MSDN etc forums, but so far, nothing back

Any help you could offer would be very very appreciated

Mark

Comments are closed