Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts

    CEll Comment Formatting, from VBA

    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 ?

  2. #2
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    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

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    GS,

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

    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Gunslinger (2016-05-28)

  5. #4
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    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 ?

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    GS,

    No # do not display leading zeros. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Gunslinger (2016-05-28)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •