Insert date and initials when doubleclicking cell

[jaco3616]

Hi guys,

I hope you can help me with an issue I have.

I want to a create a code for a workbook that allows me to insert date and name when i doubleclick a given cell.

The output in the given cell should look like this: "John Doe, 31/08-2017".
The cell range should be H:Q.

The date should just be the given date, and the name I figure could be the name that is "registered" in Excel.

I have gotten the date-thing to work using the code below, but I need help with the name and getting the two thing into the same.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("H:Q")) Is Nothing Then
    Cancel = True
    Target.Value = Date
End If
End Sub

I really hope you guys can help me.

Thanks a lot!

 

[frank_AL]

Re: Insert date and intials when doubleclicking cell

This code is working for me. I found the "LastAuthor()" solution at this link.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("H:Q")) Is Nothing Then
    Cancel = True
    Target.Value = LastAuthor() & " " & Date
End If
End Sub


Public Function LastAuthor() As String
   Application.Volatile
   LastAuthor = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

 

[jaco3616]

Re: Insert date and intials when doubleclicking cell

Hi Frank,

Thank you for your help.

This seems to work with inserting an authors name along with the date. However, the "LastAuthor()" function inserts the name of the previous user who used the workbook and not the current user.

Any idea on how to insert the current user's name instead? I'm not able to find a "CurrentAuthor()" function.

Thanks a lot!

 

[frank_AL]

Re: Insert date and intials when doubleclicking cell

Okay, let me see what I can find.

 

[frank_AL]

Re: Insert date and intials when doubleclicking cell

Found an answer at this link:
Hard for me to test since for me UserName is also LastAuthor() but give it a try.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("H:Q")) Is Nothing Then
    UserName = Application.UserName
    Cancel = True
    Target.Value = UserName & " " & Date
End If
End Sub

 

[jaco3616]

Re: Insert date and intials when doubleclicking cell

Thank you Frank! That did the trick

One final request: Is there any way to separate (line break) the UserName and Date into two lines. I.e:

John Doe
6 September 2017

If I just wanted to write this, I would write "John Doe" and then Alt+Enter and then write "6 September 2017", but I don't know how to add a line break in the output Target.Value.

 

[frank_AL]

Re: Insert date and intials when doubleclicking cell

Modify the Target Value line of code to match below:
vbCrLF tells it to all a line break

Target.Value = UserName & vbCrLf & Date