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

rtehlan

New Member
Joined
Mar 23, 2017
Messages
17
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,175
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
Joined
Mar 23, 2017
Messages
17
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
Joined
Mar 23, 2017
Messages
17
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
Joined
Mar 10, 2004
Messages
19,175
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
Joined
Mar 23, 2017
Messages
17
This is perfect ! Can't thank you enough for your valuable time :)
 

rtehlan

New Member
Joined
Mar 23, 2017
Messages
17
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,701
Messages
5,470,277
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top