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