Preserving Comment Box Formatting

[KC VBA Qns]

Hi,

How often have you commented at one cell and then add, delete, resize
rows and columns? When you set all the comments visible the comment
boxes are all over the place. If you have not been disciplined enough
check the "move but dont size with cells" option, this is what you get.

I have codes to capture the comments, delete the comment box,
re-comment with the "move but dont size with cells" option checked.
This wipes up all the original formatting.

Anyone knows how I could preserve the original formatting?

Rgds,

 

[PeterAtherton]

Hi,

How often have you commented at one cell and then add, delete, resize
rows and columns? When you set all the comments visible the comment
boxes are all over the place. If you have not been disciplined enough
check the "move but dont size with cells" option, this is what you get.

I have codes to capture the comments, delete the comment box,
re-comment with the "move but dont size with cells" option checked.
This wipes up all the original formatting.

Anyone knows how I could preserve the original formatting?

Rgds,

KC

You could use a FormatCopmments macro, this should give you some ideas

Sub FormatComment()
Dim addr As String, r As Long
Dim rng As Range, c As Variant
'there are 3 header lines in this sheet
r = Range("C4").CurrentRegion.Rows.Count + 3
addr = "C" & r
Set rng = Range("C4:" & addr)
For Each c In rng
On Error Resume Next
c.Comment.Shape.Select
Selection.ShapeRange.Height = 12.75
Next
' the last comment is selected, so..
r = Range("A1").CurrentRegion.Rows.Count
Range("A" & r + 1).Select
End Sub

Regards
Peter

 

[KC VBA Qns]

Thanks Peter.

Actually I dont quite get you.

These are my codes:

For Each CELL In Selection
If Not CELL.Comment Is Nothing Then
CELLCOMMENT = CELL.Comment.Text
CELL.ClearComments
CELL.AddComment
CELL.Comment.Shape.Placement = xlMove
CELL.Comment.Text Text:=CELLCOMMENT
End If
Next CELL

As they appear I am a very elementary VBA programmer. Could you kindly
advise how I could modify these codes?

Rgds,

 

[PeterAtherton]

Excel VB help has good examples of Comment codes. Generally you can add
comments to Cells(1,3).addcomment or cells(i,j) in a loop but to resize a
cell you have to select it and that must be done with
RAnge("A1").comment.shape.select

A range is text and we have to find what the range is. see below.

The following code adds comments to column 3 when the cell contains "Joint"
or deletes them is the code has changed. An error will occur if there is no
comment in a cell so On Error resume next is used.

Note that the text must be shown in Quotes. When its finished AddCmmts calls
the formatComments code.

Sub Addcmts()
Dim addr As String, c As Variant
Dim nr As Long, r As Long
Application.ScreenUpdating = False
' find the last used row
Selection.SpecialCells(xlCellTypeLastCell).Select
nr = ActiveCell.Row
' Cells without a comment produce an error

On Error Resume Next
For r = 4 To nr
Cells(r, 3).Select
If UCase(Cells(r, 3)) <> "JOINT" Then
Cells(r, 3).ClearComments
ElseIf UCase(Cells(r, 3)) = "JOINT" Then
With Cells(r, 3).AddComment
Cells(r, 3).Comment.Text "COG MEs:" & Chr(10)
Cells(r, 3).Comment.Visible = True
End With
End If
Next
' Call the Format code
FormatComment
Application.ScreenUpdating = True
End Sub

Sub FormatComment()
' Addr is text so Dim it String
Dim addr As String, r As Long
Dim rng As Range, c As Variant
'there are 3 header lines in this sheet
' R is the row number of the last used cell
r = Range("C4").CurrentRegion.Rows.Count + 3
' Addr is the column C and the last Row (say C20)
addr = "C" & r

'The range is RAnge(C4:c20)
Set rng = Range("C4:" & addr)
For Each c In rng
On Error Resume Next
c.Comment.Shape.Select
' do whatever you want to the format
Selection.ShapeRange.Height = 12.75
Next
' the last comment is selected, so..
r = Range("A1").CurrentRegion.Rows.Count
Range("A" & r + 1).Select
End Sub

Note: is you resize columns the comments want move and will be in the wrong
place.

Hope this is clearer
Regards
Peter