CEll Comment Formatting, from VBA

Topic

New Reply

ok, so i have some cells, which have a comment applied to each, based on specific criteria, here’s the code i have so far….

Code:

Sub Insert_Value_Comments()
    
    Dim CellRange As Range
    Set CellRange = Range("Holding_Cost")
    Dim Val As Long
    
    For Each cell In CellRange
        If cell.Value > 0 Then
            cell.ClearComments
            cell.AddComment Text:="Current Value: " & Chr(10) & " £ " & cell.Value + cell.Offset(0, 1).Value
            If cell.Value + cell.Offset(0, 1).Value >= cell.Value Then  ' set Val variable to either 5 (blue) or 3 (red)
                Val = 5
            Else
                Val = 3
            End If
            With cell.Comment.Shape
                .AutoShapeType = msoShapeRoundedRectangle  ' set comment shape
                .Line.Weight = 1.5  ' increase line thickness around comment
                .TextFrame.Characters.Font.Name = "Tahoma"
                .TextFrame.Characters.Font.Size = 9
                .TextFrame.Characters(1, 14).Font.Bold = True  ' set top line of text to bold
                .TextFrame.Characters(15, Len(cell.Comment.Text)).Font.ColorIndex = Val  ' set 2nd line of text to red/blue
                .Fill.ForeColor.RGB = RGB(135, 220, 128)  ' set background colour, Can also use ".SchemeColor = ??" instead of RGB
                .TextFrame.AutoSize = True  ' resize comment box
            End With
        Else
            cell.ClearComments
        End If
    Next cell
    
    ' Ensure comments are set to display only indicators
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

now this works, exactly as i expected, it will produce the comment as per the code, and it will look like this

Current Value:
£ 3456.25

and when the value hits a minus figure, it switches to red text, however, it does 2 things that i’d like to alter

1. the negative values, show in red, but with no “-” symbol
2. it messes with the number formats, for example £ 1350.50 will come out formatted as “£ 1350.5” ….. i don’t want to lose the trailing 0, and i’d also like to format the numerical value as £ 1,350,50 if possible.

Anyone have any idea how i can manipulate the formatting, or do i need to manipulate it whilst building the comment text ?

Reply | Quote

Replies

i got it, it wasn’t so hard when i realised i needed to format it before putting it into the comment

changed the line building the text and adding the comment to this

Code:

cell.AddComment Text:="Current Value: " & vbLf & Format(cell.Value + cell.Offset(0, 1).Value, " £ #,##0.00")

and voila, perfect, as for the minus symbol, i was getting confused, it would not show that, because the value whilst less than the cost, would not be a negative value :rolleyes:

Reply | Quote

GS,

This should get you a little closer:
Change: [noparse]cell.AddComment Text:=”Current Value: ” & Chr(10) & ” £ ” & cell.Value + cell.Offset(0, 1).Value[/noparse]
To: [noparse]Cell.AddComment Text:=”Current Value: ” & Chr(10) & Format(Cell.Value + Cell.Offset(0, 1).Value, “$###,###.00”)[/noparse]

I’m assuming you’re Windows is set up for a Location that uses Pounds as your currency symbol in which case all you have to do above is change the $ to the Pound sign, or it may convert the $ for you don’t know and can’t test.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

yeah, windows is set to Pounds as currency, however, this workbook is hard coded to Pound symbols, because the data i’m importing should be in UK currency, although, there is one point it goes horribly wrong, if the share price us for a US share and it’s in dollars!

i think i got to the answer at about the same time you did there one question though, you went for 3x “#” infront of the first “,” …..wouldn’t that display 2 leading zeros on a value ?

Reply | Quote

GS,

No # do not display leading zeros. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote