VBA: Adding a URL link into a comment

[Kopko]

I want my code in VBA to set a comment within a cell with a clickable URL iside it. I can do this manually like this:
1) Click in the cell and then insert a comment as "http://www.google.com.br"
2) Right click at the comment cell, and select Show/Hide Comments to show the comment.
Done, the cell is set as a comment showing a clickable hyperlink.

What I tried within VBA:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"H2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AddComment [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"http://www.google.com.br"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"H2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Comment[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Visible [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"H2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Comment[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Shape[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR]

</code>But this does not make the cell as an hyperlink. I still have to manually select the comment and then select another cell to make it appears as a clickable link.
I also tried:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] shp [/FONT][/COLOR][COLOR=#101094][FONT=inherit]as[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Shape
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"H2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AddComment
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Comment[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Visible [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] shp [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Comment[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Shape
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Hyperlinks[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Add Anchor[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]shp[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Address[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
                   SubAddress[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ScreenTip[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Move"[/FONT][/COLOR]</code>

This gives me this error:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Run-time error'5':
Invalid procedure callor argument</code>

Searching about it, I found that the shape object returned by comment's shape property is not supported by<code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241);">Hyperlinks.Add.</code>
But still, if I can make this works manually, I think it should work in VBA as well, or am I wrong?

Thanks for your help!

 

[Kamolga]

It does something strange. I use this to insert a comment in H2

'Lets put a comment in H2    Range("H2").AddComment
    Range("H2").comment.Visible = True
    Range("H2").comment.Text Text:="http://www.google.com.br"
    Range("H2").comment.Shape.TextFrame.AutoSize = True

It is not hyperlinked. If I enter the comment, 2x click the text (it selects it all) and get out, it is hyperlinked!

I recorded those steps and I got

'Select the shape
Range("H2").comment.Shape.Select True
'2x click the text
    Range("H2").comment.Text Text:="http://www.google.com.br"
'Select cell outside
    Range("H6").Select
'usehyperlink
    Selection.ShapeRange.Item(1).Hyperlink.Follow NewWindow:=False, AddHistory:=True

I can't get it togetther. Somehow 2clicking the text manually get Excel to understand this is a link but I can't get this reaction through VBA

 

[RoryA]

I don't know of any way to make VBA do this. It should be possible to use the Worksheet.Hyperlinks.Add method and anchor the hyperlink to the comment shape, as you can to other shapes, but it doesn't work.

 

[Kamolga]

I don't know of any way to make VBA do this. It should be possible to use the Worksheet.Hyperlinks.Add method and anchor the hyperlink to the comment shape, as you can to other shapes, but it doesn't work.

Yes I got it working with other shapes, even an arrow. Here I can't even make it work for a cell.

Here when I click on hyperlink in comment, it is from

[COLOR=#333333]ShapeRange.Item(1).Hyperlink[/COLOR]

 

[Jaafar Tribak]

@★ Kopko

This is a cheesy work-around but it should allow you to add a clickable hyperlink to a cell comment via vba without the need for selecting the comment text manually

Sub AddHyperlinkToComment( _
    ByVal Sh As Worksheet, _
    ByVal Cell As Range, _
    ByVal URL As String _
)

    Dim oCell As Range, oComment As Comment
    Dim oCurrentSheet As Worksheet, oActiveCell As Range
    
    Set oCurrentSheet = ActiveSheet
    Set oCell = CallByName(Sh, "Range", VbGet, Cell.Address)
    Set oComment = oCell.Comment
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        Sh.Activate
        Set oActiveCell = ActiveCell
        If oComment Is Nothing Then
            Set oComment = oCell.AddComment(URL)
        Else
            oComment.Text Text:=URL
        End If
        oComment.Visible = True
        oComment.Shape.TextFrame.AutoSize = True
        With oComment.Shape.TextFrame.Characters.Font
            .Name = "Tahoma"
            .Size = 9
            '.Bold = True
            .Underline = True
            .ColorIndex = 5
        End With
        oComment.Shape.Select True
        .CommandBars.FindControl(ID:=1401).Execute
        oActiveCell.Select
        oCurrentSheet.Activate
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Code usage :

Sub Test()
    Call AddHyperlinkToComment(Sh:=Sheets(1), Cell:=Range("B4"), URL:="http://www.MrExcel.com")
End Sub