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

rtehlan

New Member
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

MrExcel MVP
Try something like this...

Code:
[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

New Member
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.
 

rtehlan

New Member
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

MrExcel MVP
Try...

Code:
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

New Member
This is perfect ! Can't thank you enough for your valuable time :)
 

rtehlan

New Member
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
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top