How to: Add Smart Tags to Excel Workbooks

How to: Add Smart Tags to Excel Workbooks

You can add smart tags to Microsoft Office Excel workbooks to recognize text and give the user access to actions related to the recognized terms. The code that you write to create and configure a smart tag is the same for document-level and application-level projects, but there are some differences in the way that you associate a smart tag with workbooks. Smart tags also have different scope in document-level and application-level projects.

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2007. For more information, see Features Available by Office Application and Project Type.

This topic describes the following tasks:

To run a smart tag, end users must have smart tags enabled in Word or Excel. For more information, see How to: Enable Smart Tags in Word and Excel.

link to video For a video version of this topic, see How Do I: Add Smart Tags to Excel Workbooks.

When you add a smart tag by using a document-level customization, the smart tag is recognized only in the workbook that is associated with the customization.

To add a smart tag by using a document-level customization

  1. Create a SmartTag object, and configure this object to define the behavior of the smart tag:

    • To specify the text you want to recognize, use the Terms or Expressions properties.

    • To define the actions that users can click on the smart tag, add one or more Action objects to the Actions property.

    For more information, see Smart Tags Architecture.

  2. Add the SmartTag to the VstoSmartTags property of the ThisWorkbook class.

The following code example creates a smart tag that recognizes the word sale and the regular expression [I|i]ssue\s\d{5,6}. When the user types sale or a string that matches the regular expression (such as issue 12345) and then clicks the smart tag, it displays the cell location of the recognized text. To run this code, add the code to the ThisWorkbook class, and call the AddSmartTag method from the ThisWorkbook_Startup event handler.

NoteNote

The following example works in projects that target .NET Framework 4. To use this example in projects that target .NET Framework 3.5, see the comments in the code.


WithEvents displayAddress As Microsoft.Office.Tools.Excel.Action

Private Sub AddSmartTag()

    ' Create the smart tag for .NET Framework 4 projects.
    Dim smartTagDemo As Microsoft.Office.Tools.Excel.SmartTag = _
        Globals.Factory.CreateSmartTag(
        "www.microsoft.com/Demo#DemoSmartTag",
        "Demonstration Smart Tag")

    ' For .NET Framework 3.5 projects, use the following code to create the smart tag.
    ' Dim smartTagDemo As New  _
    '    Microsoft.Office.Tools.Excel.SmartTag( _
    '    "www.microsoft.com/Demo#DemoSmartTag", _
    '    "Demonstration Smart Tag")

    ' Specify a term and an expression to recognize.
    smartTagDemo.Terms.Add("sale")
    smartTagDemo.Expressions.Add( _
        New System.Text.RegularExpressions.Regex( _
        "[I|i]ssue\s\d{5,6}"))

    ' Create the action for .NET Framework 4 projects.
    displayAddress = Globals.Factory.CreateAction("To be replaced")

    ' For .NET Framework 3.5 projects, use the following code to create the action.
    ' displayAddress = New Microsoft.Office.Tools.Excel.Action("To be replaced")

    ' Add the action to the smart tag.
    smartTagDemo.Actions = New Microsoft.Office.Tools.Excel.Action() { _
            displayAddress}

    ' Add the smart tag.
    Me.VstoSmartTags.Add(smartTagDemo)
End Sub

Private Sub DisplayAddress_BeforeCaptionShow(ByVal sender As Object, _
    ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
    Handles DisplayAddress.BeforeCaptionShow

    Dim clickedAction As Microsoft.Office.Tools.Excel.Action = _
        TryCast(sender, Microsoft.Office.Tools.Excel.Action)

    If clickedAction IsNot Nothing Then
        clickedAction.Caption = "Display the address of " & e.Text
    End If
End Sub

Private Sub DisplayAddress_Click(ByVal sender As Object, _
    ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
    Handles DisplayAddress.Click

    Dim smartTagAddress As String = e.Range.Address( _
        ReferenceStyle:=Excel.XlReferenceStyle.xlA1)
    MsgBox("The recognized text '" & e.Text & _
            "' is at range " & smartTagAddress)
End Sub


When you add a smart tag by using an application-level add-in, you can specify whether to make the smart tag work only in a specific workbook, or in all open workbooks. Smart tags that run in all open workbooks are called application-level smart tags.

To add a smart tag to a specific workbook

  1. Create a SmartTag object, and configure this object to define the behavior of the smart tag:

    • To specify the text you want to recognize, use the Terms or Expressions properties.

    • To define the actions that users can click on the smart tag, add one or more Action objects to the Actions property.

    For more information, see Smart Tags Architecture.

  2. To create a Microsoft.Office.Tools.Excel.Workbook host item for the workbook that you want to host the smart tag, use the GetVstoObject method. For more information about creating host items, see Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time.

  3. Add the SmartTag to the VstoSmartTags property of the Microsoft.Office.Tools.Excel.Workbook.

The following code example creates a smart tag that recognizes the word sale and the regular expression [I|i]ssue\s\d{5,6}. When the user types sale or a string that matches the regular expression (such as issue 12345) and then clicks the smart tag, it displays the cell location of the recognized text. To run this code, add the code to the ThisAddIn class, call the AddSmartTagToWorkbook method from the ThisAddIn_Startup event handler, and pass a Microsoft.Office.Interop.Excel.Workbook to AddSmartTagToWorkbook.

NoteNote

The following example works in projects that target .NET Framework 4. To use this example in projects that target .NET Framework 3.5, see the comments in the code.


WithEvents displayAddress As Microsoft.Office.Tools.Excel.Action

Private Sub AddSmartTagToWorkbook(ByVal workbook As Excel.Workbook)
    ' Create a smart tag for .NET Framework 3.5 projects.
    ' Dim smartTagDemo As New  _
    '    Microsoft.Office.Tools.Excel.SmartTag( _
    '    "www.microsoft.com/Demo#DemoSmartTag", _
    '    "Demonstration Smart Tag")
    ' Create a smart tag for .NET Framework 4 projects.
    Dim smartTagDemo As SmartTag = Globals.Factory.CreateSmartTag(
        "www.microsoft.com/Demo#DemoSmartTag",
        "Demonstration Smart Tag")

    ' Specify a term and an expression to recognize.
    smartTagDemo.Terms.Add("sale")
    smartTagDemo.Expressions.Add( _
        New System.Text.RegularExpressions.Regex( _
        "[I|i]ssue\s\d{5,6}"))

    ' Create the action for .NET Framework 3.5 projects.
    ' displayAddress = New Microsoft.Office.Tools.Excel.Action( _
    '    "To be replaced")
    ' Create the action for .NET Framework 4 projects.
    displayAddress = Globals.Factory.CreateAction("To be replaced")

    ' Add the action to the smart tag.
    smartTagDemo.Actions = New Microsoft.Office.Tools.Excel.Action() { _
            displayAddress}


    ' Get the host item for the workbook in .NET Framework 3.5 projects.
    ' Dim vstoWorkbook As Microsoft.Office.Tools.Excel.Workbook = _
    '  workbook.GetVstoObject()
    ' Get the host item for the workbook in .NET Framework 4 projects.
    Dim vstoWorkbook As Microsoft.Office.Tools.Excel.Workbook = _
        Globals.Factory.GetVstoObject(workbook)

    ' Add the smart tag to the active workbook.
    vstoWorkbook.VstoSmartTags.Add(smartTagDemo)
End Sub


Private Sub DisplayAddress_BeforeCaptionShow(ByVal sender As Object, _
    ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
    Handles displayAddress.BeforeCaptionShow

    Dim clickedAction As Microsoft.Office.Tools.Excel.Action = _
        TryCast(sender, Microsoft.Office.Tools.Excel.Action)

    If clickedAction IsNot Nothing Then
        clickedAction.Caption = "Display the address of " & e.Text
    End If

End Sub

Private Sub DisplayAddress_Click(ByVal sender As Object, _
    ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
    Handles displayAddress.Click

    Dim smartTagAddress As String = e.Range.Address( _
        ReferenceStyle:=Excel.XlReferenceStyle.xlA1)
    MsgBox("The recognized text '" & e.Text & _
            "' is at range " & smartTagAddress)
End Sub


To add a smart tag that works in all open workbooks

  1. Create a SmartTag object, and configure this object to define the behavior of the smart tag:

    • To specify the text you want to recognize, use the Terms or Expressions properties.

    • To define the actions that users can click on the smart tag, add one or more Action objects to the Actions property.

    For more information, see Smart Tags Architecture.

  2. Add the SmartTag to the VstoSmartTags property of the ThisAddIn class.

The following code example creates a smart tag that recognizes the word sale and the regular expression [I|i]ssue\s\d{5,6}. When the user types sale or a string that matches the regular expression (such as issue 12345) and then clicks the smart tag, it displays the cell location of the recognized text. To run this code, add the code to the ThisAddIn class, and call the AddSmartTag method from the ThisAddIn_Startup event handler.

NoteNote

The following example works in projects that target .NET Framework 4. To use this example in projects that target .NET Framework 3.5, see the comments in the code.


WithEvents displayAddress As Microsoft.Office.Tools.Excel.Action

Private Sub AddSmartTag()

    ' Create the smart tag for .NET Framework 4 projects.
    Dim smartTagDemo As Microsoft.Office.Tools.Excel.SmartTag = _
        Globals.Factory.CreateSmartTag(
        "www.microsoft.com/Demo#DemoSmartTag",
        "Demonstration Smart Tag")

    ' For .NET Framework 3.5 projects, use the following code to create the smart tag.
    ' Dim smartTagDemo As New  _
    '    Microsoft.Office.Tools.Excel.SmartTag( _
    '    "www.microsoft.com/Demo#DemoSmartTag", _
    '    "Demonstration Smart Tag")

    ' Specify a term and an expression to recognize.
    smartTagDemo.Terms.Add("sale")
    smartTagDemo.Expressions.Add( _
        New System.Text.RegularExpressions.Regex( _
        "[I|i]ssue\s\d{5,6}"))

    ' Create the action for .NET Framework 4 projects.
    displayAddress = Globals.Factory.CreateAction("To be replaced")

    ' For .NET Framework 3.5 projects, use the following code to create the action.
    ' displayAddress = New Microsoft.Office.Tools.Excel.Action("To be replaced")

    ' Add the action to the smart tag.
    smartTagDemo.Actions = New Microsoft.Office.Tools.Excel.Action() { _
            displayAddress}

    ' Add the smart tag.
    Me.VstoSmartTags.Add(smartTagDemo)
End Sub

Private Sub DisplayAddress_BeforeCaptionShow(ByVal sender As Object, _
    ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
    Handles DisplayAddress.BeforeCaptionShow

    Dim clickedAction As Microsoft.Office.Tools.Excel.Action = _
        TryCast(sender, Microsoft.Office.Tools.Excel.Action)

    If clickedAction IsNot Nothing Then
        clickedAction.Caption = "Display the address of " & e.Text
    End If
End Sub

Private Sub DisplayAddress_Click(ByVal sender As Object, _
    ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
    Handles DisplayAddress.Click

    Dim smartTagAddress As String = e.Range.Address( _
        ReferenceStyle:=Excel.XlReferenceStyle.xlA1)
    MsgBox("The recognized text '" & e.Text & _
            "' is at range " & smartTagAddress)
End Sub


You must enable smart tags in Excel. By default, they are not enabled. For more information, see How to: Enable Smart Tags in Word and Excel.

Community Additions

ADD
Show:
© 2016 Microsoft