Tom Woodford
New Member
- Joined
- Dec 14, 2013
- Messages
- 10
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
'<<=========
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
'<<=========