How to change the position of a comment box when hovered over it?
Ok, so I have changed the position of a comment box using VBA, but it only shows this new position when I click "edit/show comment". Why does it not show this new position when I hover over the cell?
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.