Custom Smart Tags

[LEVICC01]

I have a set of commentary containing some accronyms amongst other text. I'd like to use Smart Tags to recognise these words, and provide a description from a glossary (currently stored in another sheet) that a user can see whilst highlighting one of these.

Can anyone point me in the right direction?

 

[Gary McMaster]

You can only have one hyperlink in a cell so if you only have one word per cell that needs this capability, you could set up hyperlinks as bookmarks that would take you to the definition for that word. Of course Excel, by default, will underline the entire sentence and turn it blue. You could manually (or with code) override the color and underlining for the entire sentence and then manually underline & color the single word. However, the entire sentence would still be a "hot link". If there were multiple words in a sentence you could set up a bookmark that would take you to another range on another sheet where the individual words would be listed in individual cells and linked again to their individual definitions. Unfortunately, the user would have to pick the word twice to access the definition.

The sample code below is a hack that parses the sentence character by character looking for individual words that are bolded. You could place its output into a list box. When the user selects one of the words from the list box you could lookup its definition. You could trigger the code with a cell selection change or a double click or perhaps something else.

Either way it souds like a ton of work.

Hope this helps.

Gary

Public Sub Test()

'Call this procedure from one of the events "Selection Change", "Double Click" etc
Dim iCount As Integer
Dim sOutput As String
Dim iLength As Integer
Dim sWord As String
Dim oTarget As Range

'Place a sentence in cell "A1" and bold a couple individual words in the sentence
Set oTarget = ActiveSheet.Range("A1")

For iCount = 1 To Len(oTarget.Value)
    'Bold could be italics, underline, color or whatever to highlight target words
    If oTarget.Characters(iCount, 1).Font.Bold = True Then
        sWord = sWord & oTarget.Characters(iCount, 1).Text
    Else
        If sWord <> "" Then sOutput = sOutput & sWord & ","
        'Add "sWord" to a list box here
        sWord = ""
    End If
Next iCount

'Allow user to select word in list box and lookup and display definition for word
If sOutput <> "" Then
    MsgBox "Add the following words to drop down list: " & Left(sOutput, (Len(sOutput) - 1)) 'Remove trailing comma
End If

End Sub