Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preserving Comment Box Formatting
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preserving Comment Box Formatting
"KC VBA Qns" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preserving Comment Box Formatting
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preserving Comment Box Formatting
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 "KC VBA Qns" wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preserving PivotChart Formatting | New Users to Excel | |||
Preserving cell formatting when protecting or unrprotecting | Excel Discussion (Misc queries) | |||
Preserving formatting in a formula. | Excel Discussion (Misc queries) | |||
Preserving data formatting when using VLOOKUP | Excel Programming | |||
Preserving data formatting when using VLOOKUP | Excel Programming |