Results 1 to 9 of 9

Thread: VBA for Cell Comment with User Name, Text Description, Cell Value & Date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,881
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

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

    Try something like this...

    Code:
    Option Explicit
    
    Sub AddComment()
        
        Dim sText As String
        
        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")
        
        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!

  3. #3
    New Member
    Join Date
    Mar 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,881
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

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

    Quote Originally Posted by rtehlan View Post
    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?

  5. #5
    New Member
    Join Date
    Mar 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,881
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

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

    Try...

    Code:
    Sub AddComment()
        
        Dim vCellValue As Variant
        Dim sText As String
        
        vCellValue = ActiveCell.Value
        If IsNumeric(vCellValue) Then
            vCellValue = CDbl(vCellValue)
        End If
        
        sText = Application.UserName & ":" & vbCrLf & vbCrLf
        sText = sText & "Value at Detailed Review:  (" & Format(vCellValue, "#,##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!

  7. #7
    New Member
    Join Date
    Mar 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,881
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

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

    You're very welcome! Glad I could help!

    Cheers!

  9. #9
    New Member
    Join Date
    Mar 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by rtehlan; Aug 20th, 2018 at 01:20 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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