Issues with resizing comments via macro

[Concordium]

I am new to VBA completely and especially as it relates to Excel so I am sure this is something relatively easy to figure out.

I am making a finance spreadsheet that adds comments to cells via macro. These comments contain the date that a particular macro, for that specific subtotal, was executed. My issue is that when VBA gets to the command line to resize the comments it errors out.

FWIW, I use Excel 2007 and my test code is as follows below.


Sub Total_POS()
Dim Query As String
Dim QTotal As Currency
Dim Total As Currency

Total = 0
Worksheets("Defined List").Select
Range("B12").Select

Do Until IsEmpty(ActiveCell)

Query = ActiveCell.Value

Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*" & Query & "*"

QTotal = Worksheets("Jan '09 - Dec '09").Cells(1176, "D").Value
Total = Total + QTotal

ActiveCell.Offset(1, 0).Select
Loop

Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*"
Worksheets("Totals").Select
Range("B4").Select
ActiveCell.Value = (Total)
Range("B4").AddComment
Range("B4").Comment.Visible = True
Range("B4").Comment.Text Text:="Updated on:" & Date & Chr(10) & ""
Selection.Font.Bold = True
Selection.ShapeRange.ScaleWidth 1.32, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
Range("B4").Select
Columns("A:A").EntireColumn.AutoFit
End Sub

Unfortunately bulletin boards dont like to keep the page formatting.....my apologies. Anyway, any help you guys can give me is greatly appreciated.

 

[sijpie]

If you use code tags you can keep the formatting.

For comments you have to use something differnet than recoding macros shows...

Sub Total_POS()
    Dim Query As String
    Dim QTotal As Currency
    Dim Total As Currency
    Dim cCom As Comment
    
    Total = 0
    Worksheets("Defined List").Select
    Range("B12").Select
    
    Do Until IsEmpty(ActiveCell)
        Query = ActiveCell.Value
        Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*" & Query & "*"
        QTotal = Worksheets("Jan '09 - Dec '09").Cells(1176, "D").Value
        Total = Total + QTotal
        ActiveCell.Offset(1, 0).Select
    Loop
    
    Worksheets("Jan '09 - Dec '09").Cells(2, "C").AutoFilter Field:=3, Criteria1:="*"
    Worksheets("Totals").Select
    Range("B4").Value = (Total)
    With Range("B4")
        .ClearComments
        Set cCom = .AddComment("Updated on:" & Date & Chr(10) & "")
    End With
     
    With cCom
        .Visible = False
        .Shape.Width = 120
        .Shape.Height = 20
        With .Shape.TextFrame.Characters.Font
            .Bold = True
        End With
    End With
    Range("B4").Select
    Columns("A:A").EntireColumn.AutoFit
End Sub