XL2002: How to Automate Smart Tags by Using Visual Basic for Applications (293808)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
You can use Visual Basic for Applications to programmatically add smart tags to cells, to use the smart tags' properties, to execute smart tag actions, and to remove smart tags from cells.

Adding a Smart Tag

This example adds a smart tag to cell A1 of the active workbook. You do not have to have the Label data with smart tags check box selected for this code to work properly.

NOTE: The code adds the smart tag to the cell even if the tag is not installed on your computer. However, if it is not installed, the only available actions are Remove this Smart Tag and Smart Tag Options.

Sub AddSmartTag()
       
    'Enter data to be recognized in a cell.
    Range("A1").Value = "MSFT"
       
    'Add a smart tag to the cell; this can be done even if the smart tag
    'recognizer is turned off.
    Range("A1").SmartTags.Add "urn:schemas-microsoft-com:office:smarttags#stockticker"
    
End Sub
				

Executing a Smart Tag Action

After you add a smart tag to a cell, either manually or with a VBA procedure, you can execute any of the smart tag actions by using the following code.

NOTE: You can refer to smart tag actions by using their index number (as in this case), or by using the name that the developer of the smart tag defined.

Sub ExecuteAction()
    
    Range("A1").SmartTags(1).SmartTagActions(3).Execute

End Sub
				

Removing Smart Tags

The following code sample loops through all smart tags attached to cell A1 and removes them.

Sub RemoveSmartTags()
   
    Dim st As SmartTag
        
    'Loops through each smart tag attached to the cell and deletes them.
    For Each st In Range("A1").SmartTags
        st.Delete
    Next st
        
End Sub
				

Setting Smart Tag Options

You can also use Visual Basic for Applications to change the smart tag options for the application and for the current workbook:

Sub ChangeSmartTagOptions()
    Dim stRecognizer As SmartTagRecognizer

    'Turns on smart tag recognition within the application.
    Application.SmartTagRecognizers.Recognize = True
    
    'Enable all of the smart tag recognizers listed in the Smart Tag 
    'Options dialog box.
    For Each stRecognizer In Application.SmartTagRecognizers
            stRecognizer.Enabled = True
    next stRecognizer

    'Turn on smart tag embedding in the active workbook.
    ActiveWorkbook.SmartTagOptions.EmbedSmartTags = True
    
    'Set the display mode for smart tags in the active workbook.
    ActiveWorkbook.SmartTagOptions.DisplaySmartTags = xlIndicatorAndButton
    
    'Check the entire workbook for smart tags.
    ActiveWorkbook.RecheckSmartTags

End Sub