Disable Hyperlink Hover Info?

[billpq]

I have a worksheet with hyperlinks and I would like to disable the little yellow balloon that appears when you hover over the cell with the mouse.

Can this be done? If so, can it be done on a cell by cell basis or just all of nothing for the entire sheet? If cell by cell, can I change the words that appear in the yellow balloon for those that I don't want to disable?

Thanks!

 

[Jaafar Tribak]

Do you mind sharing the source code of the dll? I would love to understand how this works. Thanks!

Sorry. it has been a long time since I wrote that very small custom c++ com dll source code and I don't know where I put it. I wrote it just for the sake of experimenting with the creation of a C dll and then using it in excel.

Furthermore, this was a x32bit dll so it won't work in x64bit applications.

If you just want to disable the tooltips that show up when hovering hyperlinks, I suggest you use the following hackish alternative which is very simple, self-contained (no dll registration required), doesn't use a win timer and works in both, x32 and x64 environments.

File Demo:
DisableHyperlinkToolTips.xlsm


Place this in the ThisWorkbook Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetThreadExecutionState Lib "Kernel32.dll" (ByVal esFlags As Long) As Long
#Else
    Private Declare Function SetThreadExecutionState Lib "Kernel32.dll" (ByVal esFlags As Long) As Long
#End If

Private WithEvents cmbrEvents As CommandBars

Public Property Let EnableHyperlinksToolTips(ByVal vNewValue As Boolean)
    If vNewValue Then
        Set cmbrEvents = Nothing
        PreventSleepMode = False
    Else
        Set cmbrEvents = Application.CommandBars
        Call cmbrEvents_OnUpdate
    End If
End Property

' ___________________________________ PRIVATE ROUTINES ___________________________________

Private Sub Workbook_Activate()
    EnableHyperlinksToolTips = False
End Sub

Private Sub Workbook_Deactivate()
    'Optionally set Property to TRUE so that the code will only apply to thisworkbook.
    EnableHyperlinksToolTips = True
End Sub

Private Property Let PreventSleepMode(ByVal bPrevent As Boolean)
    Const ES_SYSTEM_REQUIRED = &H1
    Const ES_DISPLAY_REQUIRED = &H2
    Const ES_AWAYMODE_REQUIRED = &H40
    Const ES_CONTINUOUS = &H80000000
    Const ES_FLAGS = ES_CONTINUOUS + ES_DISPLAY_REQUIRED + ES_SYSTEM_REQUIRED + ES_AWAYMODE_REQUIRED
    If bPrevent Then
        Call SetThreadExecutionState(ES_FLAGS)
    Else
        Call SetThreadExecutionState(ES_CONTINUOUS)
    End If
End Property

Private Sub cmbrEvents_OnUpdate()
    On Error Resume Next
    With Application
        .DisplayFullScreen = .DisplayFullScreen
    End With
    PreventSleepMode = True
End Sub

The code will start working upon opening\activating the workbook.

You can still turn On/Off the EnableHyperlinksToolTips Property from outside the ThisWorkbook Module using something along these lines:

Option Explicit

Sub RemoveToolTips()
    ThisWorkbook.EnableHyperlinksToolTips = False
End Sub

Sub RestoreToolTips()
    ThisWorkbook.EnableHyperlinksToolTips = True
End Sub

 

[Murmelmann]

Thanks for the detailed answer. I would like to implement similar functionality for PowerPoint but its object model does not have .DisplayFullScreen property. What does setting DisplayFullScreen to DisplayFullScreen do in this solution?

 

[Jaafar Tribak]

Thanks for the detailed answer. I would like to implement similar functionality for PowerPoint but its object model does not have .DisplayFullScreen property. What does setting DisplayFullScreen to DisplayFullScreen do in this solution?

Setting DisplayFullScreen to DisplayFullScreen was just a trick I accidently discovered that seems to lock excel and prevent it from showing screentips.

I am afraid this trick doesn't work in PPoint.

A trick that does seem to work in PPoint is to have the Shift key held down while pointing the mouse over the hyperlink(s)

Fime Demo:
Murmelmann_DisableHyperlinksScreenTips.pptm


Here is the code that worked for me:

1- In a bas module :

Option Explicit

Private Type KEYBDINPUT
    wVk              As Integer
    wScan            As Integer
    dwFlags          As Long
    time             As Long
    #If VBA7 Then
        dwExtraInfo  As LongPtr
    #Else
        dwExtraInfo  As Long
    #End If
    padding          As Currency
End Type

Private Type tagINPUT
    INPUTTYPE        As Long
    ki               As KEYBDINPUT
End Type

#If VBA7 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function SendInput Lib "USER32.DLL" (ByVal cInputs As Long, pInputs As Any, ByVal cbSize As Integer) As Long
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
#Else
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Private Declare Function SendInput Lib "USER32.DLL" (ByVal cInputs As Long, pInputs As Any, ByVal cbSize As Integer) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If

Private bShowEnd As Boolean
Private oAppEvents As CAppEvents


' _________________________________ PUBLIC ROUTINE ________________________________________

Public Sub MyAutoOpen()
    'XML on_Load Procedure.
   Set oAppEvents = New CAppEvents
   Set oAppEvents.oAppEvents = Application
End Sub

Public Sub MonitorScreenTips(ByVal bMonitor As Boolean)
    If bMonitor Then
        Call SetTimer(FindWindow("PPTFrameClass", vbNullString), 0&, 0, AddressOf ScreenTipsMonitorProc)
    Else
        bShowEnd = True
    End If
End Sub

' _________________________________ PRIVATE ROUTINES ________________________________________

Private Sub ScreenTipsMonitorProc()
    If bShowEnd Then GoTo ReleaseTimer
    Call HoldDownShiftKey
    Exit Sub
ReleaseTimer:
    Call KillTimer(FindWindow("PPTFrameClass", vbNullString), 0)
    bShowEnd = False
    Debug.Print "Done - Timer safely released."
End Sub

Private Sub HoldDownShiftKey()
    Const KEYEVENTF_KEYUP = &H2, KEYEVENTF_UNICODE = &H4, VK_SHIFT = &H10
    ReDim InputArray(2&) As tagINPUT
    InputArray(0&).INPUTTYPE = 1&
    InputArray(0&).ki.wVk = VK_SHIFT
    InputArray(0&).ki.dwFlags = KEYEVENTF_UNICODE
    InputArray(1&).INPUTTYPE = 1&
    InputArray(1&).ki.wVk = VK_SHIFT
    InputArray(1&).ki.dwFlags = KEYEVENTF_UNICODE + KEYEVENTF_KEYUP
    Call SendInput(2&, InputArray(0&), LenB(InputArray(0&)))
End Sub

2- Class Module code (CAppEvents)

Option Explicit

Public WithEvents oAppEvents As Application

Private Sub oAppEvents_SlideShowBegin(ByVal Wn As SlideShowWindow)
    DisableHyperlinksToolTips = True
End Sub

Private Sub oAppEvents_SlideShowEnd(ByVal Pres As Presentation)
    DisableHyperlinksToolTips = False
    MsgBox "slide ending"
End Sub

Private Sub oAppEvents_PresentationClose(ByVal Pres As Presentation)
    DisableHyperlinksToolTips = False
End Sub

Private Property Let DisableHyperlinksToolTips(ByVal vNewValue As Boolean)
    Call MonitorScreenTips(bMonitor:=vNewValue)
End Property

 

[Murmelmann]

Hi thanks and sorry for the delayed response. It tried this in Office 365 in a VSTO addin and it did not work. What office version are you on? I guess that the window name has changed from "PPTFrameClass". Where or how did you get the PPTFrameClass name?

 

[Murmelmann]

So is "PPTFrameClass" the PowerPoint main window and you disable tooltips in an active slideshow by holding down the shift key? That does not work for what I want to do, I want to disable tooltips not only when a slideshow is showing.