VBA, auto populate Multi user initial with date and time stamp in comment box

[Nicki]

Hi, Could you please let me know if it is possible to have such a code for auto populating user initial and date/time by inserting the comment box in any cell in excel 2010. Please see picture attached. At the moment users will put date/time manually.
Many thanks for your help

 

[vletm]

Nicki
Many things are possible,
like with this sample file.
> Activate any cell and press [Add Comment]-button.
If need more 'comments' to same cell then do previous again.

 

[Belleke]

Try this ( for column N as in your picture)
Code in the sheet where you want the comments)

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    If Target = "" Then
        preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

 

[Nightlytic]

Initian & date In currently selected cell, or top left corner if a range is selected.

Sub Macro2()

On Error Resume Next
With ActiveCell
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Application.UserName & " " & Date & Chr(10) & ""
End With

With ActiveCell
.Comment.Visible = False
.Comment.Text Text:=Application.UserName & " " & Date & Chr(10) & ""
End With


End Sub

 

[Nicki]

Nicki
Many things are possible,
like with this sample file.
> Activate any cell and press [Add Comment]-button.
If need more 'comments' to same cell then do previous again.

Nicki
Many things are possible,
like with this sample file.
> Activate any cell and press [Add Comment]-button.
If need more 'comments' to same cell then do previous again.

Thanks vletm. but how can i use a short key instead of "Add Button".

 

[vletm]

Nicki ... hmm ...
As other Customize Keyboard ShortCuts ... never tested myself!
That Marco's name is Sheet1!Add_ComS.

Ps. If need more more 'comments' to add in one 'comment', then there would be other possibilities.

 

[Nicki]

Try this ( for column N as in your picture)
Code in the sheet where you want the comments)

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    If Target = "" Then
        preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

Belleke could you use this code in an excel file please i did not get it.

 

[Nicki]

Nicki
Many things are possible,
like with this sample file.
> Activate any cell and press [Add Comment]-button.
If need more 'comments' to same cell then do previous again.

vletm, please could you create line space between each use

Nicki
Many things are possible,
like with this sample file.
> Activate any cell and press [Add Comment]-button.
If need more 'comments' to same cell then do previous again.

 

[vletm]

Nicki
I add empty row between comments like below
... but ...
as You would notice,
there will be next challenge; 'fixed comment box size'.
But (again) that would solve too!
... depends how long and many comments would be there.

 

[Nicki]

Nicki
I add empty row between comments like below
View attachment 48830 ... but ...
as You would notice,
there will be next challenge; 'fixed comment box size'.
But (again) that would solve too!
... depends how long and many comments would be there.

Nicki
I add empty row between comments like below
View attachment 48830 ... but ...
as You would notice,
there will be next challenge; 'fixed comment box size'.
But (again) that would solve too!
... depends how long and many comments would be there.

I am sorry I can not see any changes in the file, have u uploaded the right one or is it me being stupid

 

[vletm]

Nicki
You would answer that Yourself (It's against policy of Chandoo).

Above is a hint of results of those versions.

 

[Nicki]

Nicki
You would answer that Yourself (It's against policy of Chandoo).
View attachment 48844
Above is a hint of results of those versions.

Many Thanks got it