Hyperlink within comment box in excel

[JJ_AZ]

would some one know how to insert a hyperlink for a file inside the comment box in excel? Formatting comment text does not provide the option for hyperlink. Thx.

 

[Tom Urtis]

Example, for any selected cell, press Insert > Comment. If your user name appears in the comment text, clear it with your mouse and enter your url, such as "www.cnn.com" (without the quotes) or the sheet name and range name if this is to be an internal link (in the required syntax as if this were such an internal link).

Next, reselect the cell and right click on it, then from that pop-up menu select "Show/Hide Comments" to keep the comment shape visible. This would have been a requirement anyway because once the mouse is not set over the subject cell, the comment would have disappeared so the comment shape needs to be visible in order to be click on, unless you always set it directly over the cell, which would not be likely as the cell's text would then be blocked from view. Were that not desired, a simple cell Selection or other event could have done the job, but since it's a comment shape as the visual url-containing interface, the comment must be visible.

Here is the trick, select the interior comment shape and take a very close look at that comment text you just typed in. Yes it displays what you typed in, such as "www.cnn.com", but really what that shape *might* contain is "www.cnn.com" & Chr(10) & "". In other words, a carriage return and empty character were added to the shape even though you did not enter them yourself with the url. So, with the comment selected, press Alt+End to go to the true end of the comment text, then hit the backspace key once.

Finally, exit the comment shape by selecting any worksheet cell, then when you mouse over (or click on) that comment shape it will act as a hyperlink to your typed-in url. You might optionally want to adjust the shape of the comment box so it appropriately fits the long and low shape of a single-text line url, but this is the basic method to do what you are asking about.

I just tested this on XP again and it worked for me no problem.

 

[sarvesh1608]

Sir,
I have tried your method. It worked perfectly fine for Web URL HyperLinks. But it is not working for internal links. I want to give link of a cell in same workbook but every time I tried, Excel considered link as text.

I am using Excel 2016 on Windows 10 64 Bit.
Please give the solution.

Thanks in Advance.

 

[Jaafar Tribak]

After the comment has been setup like an hyperlink as described by Tom Urtis, try running this little code example : (Change the sheet name and range addresses to suit)

#If VBA7 Then
    Declare PtrSafe Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
#Else
    Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
#End If

Const COLOR_HOTLIGHT = 26

Sub Test()
    Sheet1.Range("F10").Comment.Text "Click Comment to select Cell A1"
    With Sheet1.Range("F10").Comment.Shape
        .Hyperlink.Address = ""
        .Hyperlink.SubAddress = "Sheet1!A1"
        .Hyperlink.ScreenTip = "Jump"
        .TextFrame.Characters.Font.Color = GetSysColor(COLOR_HOTLIGHT)
        .TextFrame.Characters.Font.Underline = True
        .TextFrame.Characters.Font.Bold = False
        .TextFrame.AutoSize = True
    End With
End Sub