How to: Add Smart Tags to Excel Workbooks

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Document-level projects Excel 2003 Excel 2007 Application-level projects Excel 2007 For more information, see Features Available by Application and Project Type.

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.

This topic describes the following tasks:

• Adding smart tags in document-level customizations

• Adding smart tags in application-level add-ins

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.

Adding Smart Tags in Document-Level Customizations

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.

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

Private Sub AddSmartTag()
    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.
    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

private Microsoft.Office.Tools.Excel.Action displayAddress;

private void AddSmartTag()
{
    Microsoft.Office.Tools.Excel.SmartTag smartTagDemo =
        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.
    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. 
    this.VstoSmartTags.Add(smartTagDemo);

    displayAddress.BeforeCaptionShow += new 
        Microsoft.Office.Tools.Excel.BeforeCaptionShowEventHandler(
        DisplayAddress_BeforeCaptionShow);

    displayAddress.Click += new 
        Microsoft.Office.Tools.Excel.ActionClickEventHandler(
        DisplayAddress_Click);
}

void DisplayAddress_BeforeCaptionShow(object sender, 
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    Microsoft.Office.Tools.Excel.Action clickedAction =
        sender as Microsoft.Office.Tools.Excel.Action;

    if (clickedAction != null)
    {
        clickedAction.Caption = "Display the address of " +
            e.Text;
    }
}

void DisplayAddress_Click(object sender, 
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    string smartTagAddress = e.Range.get_Address(missing,
        missing, Excel.XlReferenceStyle.xlA1, missing, missing);
    System.Windows.Forms.MessageBox.Show("The recognized text '" + e.Text +
        "' is at range " + smartTagAddress);
}

Adding Smart Tags in Application-Level Add-Ins

Starting in SP1, you can 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. Use the GetVstoObject method to create a Microsoft.Office.Tools.Excel.Workbook host item for the workbook you want to host the smart tag. For more information about creating host items, see Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time.

   Note

   If you are using a project that you created before you installed SP1, you must modify the project before you can use the GetVstoObject method. For more information, 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 Workbook to AddSmartTagToWorkbook.

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

Private Sub AddSmartTagToWorkbook(ByVal workbook As Excel.Workbook)
    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.
    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 to the active workbook. 
    Dim vstoWorkbook As Microsoft.Office.Tools.Excel.Workbook = _
        workbook.GetVstoObject()
    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

private Microsoft.Office.Tools.Excel.Action displayAddress;

private void AddSmartTagToWorkbook(Excel.Workbook workbook)
{
    Microsoft.Office.Tools.Excel.SmartTag smartTagDemo =
        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.
    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 to the active workbook.
    Microsoft.Office.Tools.Excel.Workbook vstoWorkbook =
        workbook.GetVstoObject();
    vstoWorkbook.VstoSmartTags.Add(smartTagDemo);

    displayAddress.BeforeCaptionShow += new
        Microsoft.Office.Tools.Excel.BeforeCaptionShowEventHandler(
        DisplayAddress_BeforeCaptionShow);

    displayAddress.Click += new
        Microsoft.Office.Tools.Excel.ActionClickEventHandler(
        DisplayAddress_Click);
}

void DisplayAddress_BeforeCaptionShow(object sender,
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    Microsoft.Office.Tools.Excel.Action clickedAction =
        sender as Microsoft.Office.Tools.Excel.Action;

    if (clickedAction != null)
    {
        clickedAction.Caption = "Display the address of " +
            e.Text;
    }
}

void DisplayAddress_Click(object sender,
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    string smartTagAddress = e.Range.get_Address(missing,
        missing, Excel.XlReferenceStyle.xlA1, missing, missing);
    System.Windows.Forms.MessageBox.Show("The recognized text '" + e.Text +
        "' is at range " + smartTagAddress);
}

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.

   Note

   If you are using a project that you created before you installed SP1, you must modify the project to generate the VstoSmartTags property. For more information, see How to: Add Application-Level Smart Tags to Projects That Were Created Before SP1.

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.

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

Private Sub AddSmartTag()
    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.
    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

private Microsoft.Office.Tools.Excel.Action displayAddress;

private void AddSmartTag()
{
    Microsoft.Office.Tools.Excel.SmartTag smartTagDemo =
        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.
    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. 
    this.VstoSmartTags.Add(smartTagDemo);

    displayAddress.BeforeCaptionShow += new 
        Microsoft.Office.Tools.Excel.BeforeCaptionShowEventHandler(
        DisplayAddress_BeforeCaptionShow);

    displayAddress.Click += new 
        Microsoft.Office.Tools.Excel.ActionClickEventHandler(
        DisplayAddress_Click);
}

void DisplayAddress_BeforeCaptionShow(object sender, 
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    Microsoft.Office.Tools.Excel.Action clickedAction =
        sender as Microsoft.Office.Tools.Excel.Action;

    if (clickedAction != null)
    {
        clickedAction.Caption = "Display the address of " +
            e.Text;
    }
}

void DisplayAddress_Click(object sender, 
    Microsoft.Office.Tools.Excel.ActionEventArgs e)
{
    string smartTagAddress = e.Range.get_Address(missing,
        missing, Excel.XlReferenceStyle.xlA1, missing, missing);
    System.Windows.Forms.MessageBox.Show("The recognized text '" + e.Text +
        "' is at range " + smartTagAddress);
}

Security

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.

See Also

Tasks

How to: Enable Smart Tags in Word and Excel

How to: Add Smart Tags to Word Documents

How to: Add Application-Level Smart Tags to Projects That Were Created Before SP1

How to: Create Smart Tags With Custom Recognizers in Word

How to: Create Smart Tags With Custom Recognizers in Excel

Walkthrough: Creating a Smart Tag by Using a Document-Level Customization

Walkthrough: Creating a Smart Tag by Using an Application-Level Add-In

Concepts

Smart Tags Overview

Smart Tags Architecture

Developing Office Solutions

Other Resources

How Do I: Add Smart Tags to Excel Workbooks?

Change History

Date	History	Reason
July 2008	Added new procedures for application-level add-ins.	SP1 feature change.