How to change the position of a comment box when hovered over it?

excel, vba

Solution

By default, you cannot make the comment show at a pre-defined location when you hover on a cell. Having said that if we sort of create a loop in the code which constantly captures the Mouse Co-Ordinates then yes it is possible to achieve what we want. Still this is not an ideal solution as any loop slows down your workbook.

I am posting this solution to only demonstrate that it is possible.

This code uses the GetCursorPos API. You may read about the API in the link that I mentioned which also happens to be my fav site for APIs :)

Let's say, Cell C4 has a comment

Now Paste this code in a module.

Option Explicit

Public Declare Function GetCursorPos Lib "user32" _
(lpPoint As POINTAPI) As Long

Public Type POINTAPI
    x As Long
    y As Long
End Type

Dim lngCurPos As POINTAPI
Public CancelHover As Boolean
Dim C4_Left As Double, C4_Right As Double, C4_Top As Double, C4_Bottom As Double

Public Sub ActivateHover()
    CancelHover = False

    With ActiveWindow
        C4_Left = .PointsToScreenPixelsX(Range("C4").Left)
        C4_Right = .PointsToScreenPixelsX(Range("C4").Offset(0, 1).Left)
        C4_Top = .PointsToScreenPixelsY(Range("C4").Top)
        C4_Bottom = .PointsToScreenPixelsY(Range("C4").Offset(1, 0).Top)
    End With

    Do
        GetCursorPos lngCurPos

        If lngCurPos.x > C4_Left And lngCurPos.x < C4_Right Then
            If lngCurPos.y > C4_Top And lngCurPos.y < C4_Bottom Then
                '~~> Show the comment forcefully
                Range("C4").Comment.Visible = True
                '~~> Re-position the comment. Can use other properties as .Left etc
                Range("C4").Comment.Shape.Top = 100
            Else
                Range("C4").Comment.Visible = False
            End If
        End If

        DoEvents
    Loop Until CancelHover = True
End Sub

Add a button on the worksheet and in the click event of the button add this code which will stop the above loop.

Private Sub CommandButton1_Click()
    CancelHover = True
End Sub

Now when you hover the mouse over the cell, the comment will move to the pre-defined position.

Note: I am still trying to perfect the code at it is still not very accurate. The `PointsToScreenPixelsX` is not giving me accurate dimensions obviously so the comment sometimes shows even when I hover on say `B3`. Like I said, I am trying to perfect that.

Problem

Original source