VBA for Cell Comment with User Name, Text Description, Cell Value & Date

[rtehlan]

Hi,

Any help on this would be greatly appreciated, i have already spent so much time on this & nothing is of any help. I need a VBA which i can run on a selected cell & get the cell comment with user name, Text Description with Cell Value & Current date just like below:

Tehlan, Romil:

Value at Detailed Review : (106,506)
Date: 3/23/2017


Thank you
Romil

 

[Domenic]

Try something like this...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] AddComment()
    
    [color=darkblue]Dim[/color] sText [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    sText = Application.UserName & ":" & vbCrLf & vbCrLf
    sText = sText & "Value at Detailed Review:  (" & Format(Range("A2").Value, "#,##0") & ")" & vbCrLf
    sText = sText & "Date:  " & Format(Date, "m/dd/yyyy")
    
    [color=darkblue]With[/color] ActiveCell
        .ClearComments
        [color=darkblue]With[/color] .AddComment
            .Text sText
            [color=darkblue]With[/color] .Shape
                .TextFrame.Characters(1, InStr(sText, ":")).Font.Bold = msoTrue
                .Width = 180
                .Height = 60
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[rtehlan]

Thank you so much, Almost Working perfectly but only it is picking the cell value from "Cell A2" instead of that particular cell i select & run the code.

 

[Domenic]

Thank you so much,

You're very welcome!

Almost Working perfectly but only it is picking the cell value from "Cell A2" instead of that particular cell i select & run the code.

If the selected cell is empty/blank, how do you want the code to proceed?

 

[rtehlan]

i will be not be using this on any blank cell or a range. rather than on 1 cell at a time with a value. even if i just use this on a blank cell i would want 0 as the value on Detailed Review.

 

[Domenic]

Try...

Sub AddComment()
    
    [COLOR=#ff0000]Dim vCellValue As Variant[/COLOR]
    Dim sText As String
    
    [COLOR=#ff0000]vCellValue = ActiveCell.Value
    If IsNumeric(vCellValue) Then
        vCellValue = CDbl(vCellValue)
    End If[/COLOR]
    
    sText = Application.UserName & ":" & vbCrLf & vbCrLf
    sText = sText & "Value at Detailed Review:  (" & Format([COLOR=#ff0000]vCellValue[/COLOR], "#,##0") & ")" & vbCrLf
    sText = sText & "Date:  " & Format(Date, "m/dd/yyyy")
    
    With ActiveCell
        .ClearComments
        With .AddComment
            .Text sText
            With .Shape
                .TextFrame.Characters(1, InStr(sText, ":")).Font.Bold = msoTrue
                .Width = 180
                .Height = 60
            End With
        End With
    End With
    
End Sub

Hope this helps!

 

[rtehlan]

This is perfect ! Can't thank you enough for your valuable time

 

[Domenic]

You're very welcome! Glad I could help!

Cheers!

 

[rtehlan]

Adding to this, If there is any way each time i use this macro it does not supersede the existing one & adds the comments above or below the existing text on comment box ? So that there is a history

For Eg.
Tehlan, Romil:

Value at Detailed Review : (106,506)
Date: 3/23/2017

Tehlan, Romil:

Value at Detailed Review : (100,000)
Date: 8/20/2018

& So on. Also is there anyway to change the color of the comment box instead of default pale yellow color in the code ? Wanted to distinguish these comments from other