Excel comments appear only as a thin black line and I cannot access the comments via the edit function

[Tom Woodford]

I have a budget worksheet that I have been using for about 7-8 years, it is large and there are many comments in it.
Last year the comments were all over the place and I found, installed, and used a VBA command to bring them back in line and it worked great.
Well I had to do some serious rearranging this year and they were all over the place again and many were appearing only as a thin black line that I can not seem to
do anything with at all. I found another command that purported to fix this and it runs but it is not resolving anything for me. it follows below.

'==========>>
Option Explicit

'--------->>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range, rCell As Range

Set WB = ActiveWorkbook

For Each SH In WB.Worksheets
On Error Resume Next
Set Rng = SH.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
With rCell.Comment.Shape
.Left = rCell.Left + rCell.Width
.Top = rCell.Top
End With
Next rCell
End If
Next SH
End Sub
'<<=========

 

[DanteAmor]

Try this:

Public Sub Tester()
  Dim WB As Workbook
  Dim SH As Worksheet
  Dim Rng As Range, rCell As Range
 
  Set WB = ActiveWorkbook
 
  For Each SH In WB.Worksheets
    On Error Resume Next
    Set Rng = SH.Cells.SpecialCells(xlCellTypeComments)
    On Error GoTo 0
   
    If Not Rng Is Nothing Then
      For Each rCell In Rng.Cells
        With rCell.Comment.Shape
          .Left = rCell.Left + rCell.Width + 5
          .Top = rCell.Top
          .TextFrame.AutoSize = True
        End With
      Next rCell
    End If
  Next SH
End Sub

 

[Tom Woodford]

Thanks but I got the following error

 

[DanteAmor]

Try this:

Sub fit_comments()
  Dim sh As Worksheet
  Dim cmt As Comment
  
  For Each sh In ActiveWorkbook.Sheets
    For Each cmt In sh.Comments
      With cmt.Shape
        .Left = cmt.Parent.Left + cmt.Parent.Width + 5
        .Top = cmt.Parent.Top
        .TextFrame.AutoSize = True
      End With
    Next
  Next
End Sub

 

[Tom Woodford]

Same thing, am I doing soething else wrong

 

[DanteAmor]

Something in one of your sheets has a problem.
Maybe an object in one of your sheets doesn't accept the update, because in my tests all comments are updated.

If you know how to run the macro in debug mode, that is, step by step by pressing the F8 key, then run the following macro.
The macro will select each sheet, then select each comment and apply the update to it. When the macro stops it analyzes which is the sheet and which object is selecting or which object the macro stopped.

Sub fit_comments()
  Dim sh As Worksheet
  Dim cmt As Comment
  
  For Each sh In ActiveWorkbook.Sheets
    sh.Select
    For Each cmt In sh.Comments
      With cmt.Shape
        .Select
        .Left = cmt.Parent.Left + cmt.Parent.Width + 5
        .Top = cmt.Parent.Top
        .TextFrame.AutoSize = True
      End With
    Next
  Next
End Sub

----- --
Or you can avoid the error with the following, if there is an object with problems then it will be ignored, but all other comments will be adjusted:

Sub fit_comments_2()
  Dim sh As Worksheet
  Dim cmt As Comment
  
  On Error Resume Next
  For Each sh In ActiveWorkbook.Sheets
    For Each cmt In sh.Comments
      With cmt.Shape
        .Left = cmt.Parent.Left + cmt.Parent.Width + 5
        .Top = cmt.Parent.Top
        .TextFrame.AutoSize = True
      End With
    Next
  Next
End Sub