@dkyrtata
I agree that it is something that should be improved upon, in my opinion the error message is useless and means nothing in the context of this exact issue (if you didn't point out the Comments I would not have realized it myself since they were hidden). Perhaps the 'canvas/out of bounds' area is read-only (since the old original Excel days. I guess we will not really ever know.
Regarding your manual movements, perhaps I have a small solution that you can adapt and use for your own use case.
I created something that may help you to start of with if you still may have need of it:
The below VBA code is just a starting point to show you that it is indeed possible with VBA, for your use case in your specific file you would need to modify further.
SETUP
The references mentioned here are used in the VBA code below
1. Create a new Workbook, inside it create a new Worksheet "TEST WORKSHEET"
2. Give a range of cells a named range "TEST_DATA"
3. Inside VBA (ALT+F11) create a new Module (right click on Modules folder -> create new Module)
4. Paste the code, and after reviewing it press F5 on your test file to see how it moves the stuff.
NOTE: THE HOVER EFFECT OF THE COMMENTS DOES NOT SEEM TO CAUSE THE ERROR WE ARE DISCUSSING HERE.
(WHEN YOU RIGHT-CLICK -> Edit notes) you will notice the comment box has moved slightly to the left and downwards.
Code does this:
- Loops through the Range of Cells and try to find non-empty cells; and then try to find if they have comments attached.
- For found comments it will Debug.Line to the VBA console but additionally move the comment box left-downwards relative to the active cell.
In theory this helps you with moving the comments such that you can hide right-most columns (even those close/overlapping the hover effect) properly.
I created this as test code to see if it is possible to move the Comments boxes, and have since modified this much more into a working solution for my specific Excel file.
NOTE:
Inside the range of cells of TEST_DATA, you can have some empty values, and then add 'the old legacy style comments'. If your workbooks is actually using Conversations; then the object is different (I think it is `cCell.CommentThreaded` but haven't tested to see how it works differently).
CODE
Public Sub ObjectInformation()
Dim ws As Worksheet
' Change Worksheet name and Cell Range/Address ($A$Z format)
Dim nameWorksheet As String: nameWorksheet = "TEST WORKSHEET"
Dim nameDataRange As String: nameDataRange = "TEST_DATA"
Set ws = ThisWorkbook.Worksheets(nameWorksheet)
Dim rRange As Range
Dim cCell As Range
Set rRange = ws.Range(nameDataRange)
For Each cCell In rRange
' Skip empty cells
If cCell.Value <> "" Then
' Check for Notes (legacy comments)
If Not cCell.comment Is Nothing Then
Debug.Print "Note in cell " & cCell.Address & ": '" & cCell.comment.Text & "'"
' Attempt to report and update (move) bounds of Comment for current Cell
getCommentBounds cCell.comment, cCell
' Attempt to report current Cell bounds information
getCellBounds cCell
End If
End If
Next
End Sub
Private Sub getCommentBounds(ByRef comment As comment, ByRef rCell As Range, Optional ByVal moveAndResizeToCell As Boolean = True)
Dim commentShape As Shape: Set commentShape = comment.Shape
Debug.Print "OLD Comment Details:"
' Get location (top-left corner) and size (width and height)
Debug.Print " Top: " & commentShape.Top
Debug.Print " Left: " & commentShape.Left
Debug.Print " Width: " & commentShape.Width
Debug.Print " Height: " & commentShape.Height
' Move Comment?
If moveAndResizeToCell Then
' First reset shape to 0,0 of current Cell
commentShape.Left = rCell.Left
commentShape.Top = rCell.Top
' Non-hoverable (edit Comment) movement; move to Left of Cell (-10) and Downwards (10)
commentShape.IncrementLeft -10
commentShape.IncrementTop 10
' Reduce Comment such that it can at least fit inside the current Cell
commentShape.Width = rCell.Width * 0.95
Debug.Print "NEW Comment Details:"
' Get location (top-left corner) and size (width and height)
Debug.Print " Top: " & commentShape.Top
Debug.Print " Left: " & commentShape.Left
Debug.Print " Width: " & commentShape.Width
Debug.Print " Height: " & commentShape.Height
End If
End Sub
Private Sub getCellBounds(ByRef cell As Range)
Debug.Print "Cell Details:"
' Get location (top-left corner) and size (width and height)
Debug.Print " Top: " & cell.Top
Debug.Print " Left: " & cell.Left
Debug.Print " Width: " & cell.Width
Debug.Print " Height: " & cell.Height
End Sub
