Remove popup balloon when hovering over hyperlink

[nappyjim]

Hi.

I have excel 2016, I want to remove the balloon that comes when you hover over a hyperlink. I know I can put a space into the screentip, but the balloon still shows. I dont want the balloon at all.

I found an old thread from 2010 where jafaar made a vba to do this, but even when I downloaded his XLS,I got an error when trying to run it so Im guessing its different 2010 to 2016?

Because im a new user, I cant attach my example excel.

 

[My Aswer Is This]

Try this:
Add a comment to the same cell with the Hyperlink
Then right click the cell and choose Edit comment
Then Right click the comment Border
Make sure you right click on comment border
Then choose Format comment.
Choose Colors and lines
Set fill comment color to no fill.
Set Line color to White
Remove any Text from comment
Choose Hide Comment
When you click on Hyperlink you should not see the box you do not want to see.

If you want to do this several times just copy the cell paste it where you want and change the hyperlink.
Keeps you from having to go through this process for each Hyperlink

 

[Jaafar Tribak]

Try this:
Add a comment to the same cell with the Hyperlink
Then right click the cell and choose Edit comment
Then Right click the comment Border
Make sure you right click on comment border
Then choose Format comment.
Choose Colors and lines
Set fill comment color to no fill.
Set Line color to White
Remove any Text from comment
Choose Hide Comment
When you click on Hyperlink you should not see the box you do not want to see.

If you want to do this several times just copy the cell paste it where you want and change the hyperlink.
Keeps you from having to go through this process for each Hyperlink

Nice trick My Answer Is This,

I would also set both the height and width of the comment to 0 otherwise there is shadow of the comment outline left behind .

 

[My Aswer Is This]

That's true Original poster:
Set Height and width to 0

I never saw shadow because all my cells were white

 

[Jaafar Tribak]

Here is a vba workaround that doesn't require adding comments to the cells and therefore the cells with the hyperlinks won't display the distracting red comment triangle.

Setting the DisableAllHyperlinkScreenTips Property to TRUE will disable all hyperlink screenTips throughout the workbook
Setting the DisableHyperlinkScreenTip Property to TRUE will disable only the screentiips for specific cells of your choice.

1- Code in the ThisWorkbook Module:

Option Explicit

Private Enum Scope
    IndividualHyperlinks
    AllHyperlinks
End Enum

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private WithEvents oCmbrsEvents As CommandBars

Private eScope As Scope


Public Property Let DisableAllHyperlinkScreenTips(ByVal Disable As Boolean)
    eScope = AllHyperlinks
    If Disable Then
        Set oCmbrsEvents = Application.CommandBars
        oCmbrsEvents_OnUpdate
    Else
        Set oCmbrsEvents = Nothing
    End If
End Property


Public Property Let DisableHyperlinkScreenTip(ByVal Range As Range, ByVal Disable As Boolean)
    Dim oCell As Range
    
    eScope = IndividualHyperlinks
    If Disable Then
        For Each oCell In Range
            oCell.ID = "HyperlinkDisabled"
        Next
        Set oCmbrsEvents = Application.CommandBars
        oCmbrsEvents_OnUpdate
    Else
        For Each oCell In Range
            oCell.ID = ""
        Next
        Set oCmbrsEvents = Nothing
    End If
End Property


Private Sub oCmbrsEvents_OnUpdate()
    Dim tCurPos As POINTAPI
    Dim oCell As Variant

    GetCursorPos tCurPos

    Set oCell = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)

    If TypeName(oCell) = "Range" Then
        If eScope = IndividualHyperlinks Then
            If oCell.ID = "HyperlinkDisabled" Then
                With Application: .DisplayFullScreen = .DisplayFullScreen: End With
            End If
        Else
            With Application: .DisplayFullScreen = .DisplayFullScreen: End With
        End If
    End If
    With Application.CommandBars.FindControl(ID:=2020): .Enabled = Not .Enabled: End With
End Sub

2- Code Usage example:

Sub Test1()
    ThisWorkbook.DisableAllHyperlinkScreenTips = True
End Sub

Sub Test2()
    ThisWorkbook.DisableHyperlinkScreenTip(Sheet1.Range("A1:A10")) = True
End Sub

 

[My Aswer Is This]

Yes I have seen code like this before.
My answer was a easy solution but not a perfect solution.

 

[2Took]

Here is a vba workaround that doesn't require adding comments to the cells and therefore the cells with the hyperlinks won't display the distracting red comment triangle.

Setting the DisableAllHyperlinkScreenTips Property to TRUE will disable all hyperlink screenTips throughout the workbook
Setting the DisableHyperlinkScreenTip Property to TRUE will disable only the screentiips for specific cells of your choice.

1- Code in the ThisWorkbook Module:

Option Explicit

Private Enum Scope
    IndividualHyperlinks
    AllHyperlinks
End Enum

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL]
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private WithEvents oCmbrsEvents As CommandBars

Private eScope As Scope


Public Property Let DisableAllHyperlinkScreenTips(ByVal Disable As Boolean)
    eScope = AllHyperlinks
    If Disable Then
        Set oCmbrsEvents = Application.CommandBars
        oCmbrsEvents_OnUpdate
    Else
        Set oCmbrsEvents = Nothing
    End If
End Property


Public Property Let DisableHyperlinkScreenTip(ByVal Range As Range, ByVal Disable As Boolean)
    Dim oCell As Range
   
    eScope = IndividualHyperlinks
    If Disable Then
        For Each oCell In Range
            oCell.ID = "HyperlinkDisabled"
        Next
        Set oCmbrsEvents = Application.CommandBars
        oCmbrsEvents_OnUpdate
    Else
        For Each oCell In Range
            oCell.ID = ""
        Next
        Set oCmbrsEvents = Nothing
    End If
End Property


Private Sub oCmbrsEvents_OnUpdate()
    Dim tCurPos As POINTAPI
    Dim oCell As Variant

    GetCursorPos tCurPos

    Set oCell = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)

    If TypeName(oCell) = "Range" Then
        If eScope = IndividualHyperlinks Then
            If oCell.ID = "HyperlinkDisabled" Then
                With Application: .DisplayFullScreen = .DisplayFullScreen: End With
            End If
        Else
            With Application: .DisplayFullScreen = .DisplayFullScreen: End With
        End If
    End If
    With Application.CommandBars.FindControl(ID:=2020): .Enabled = Not .Enabled: End With
End Sub

2- Code Usage example:

Sub Test1()
    ThisWorkbook.DisableAllHyperlinkScreenTips = True
End Sub

Sub Test2()
    ThisWorkbook.DisableHyperlinkScreenTip(Sheet1.Range("A1:A10")) = True
End Sub

It's insane that it takes this much for what should be an option click