Adding auto name & number notes to a cell

[marlowem]

Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I was wondering is fanyone can help me please? I'm after a Marco that when ran, will populate a cell with the following;<o></o>
<o></o>
(auto number) - (user initials or registered name) - (static date) <o></o>
<o></o>
However, what I'd also like is that the contents of the cell are recognised and the next time the macro is ran in the same cell the following happens.<o></o>
<o></o>
(carriage return)
(auto number) - (user initials or registered name) - (static date) <o></o>
<o></o>
<o></o>
Any help greatly appreciated!!<o></o>
<o></o>
Thanks<o></o>
<o></o>
<o></o>

 

[VoG]

This is a bit unclear. Do you want to add this directly to a cell or in a comment in the cell. What should trigger this action?

 

[marlowem]

Hi,
Apologies. I would like to add this directly to a cell and the trigger would be the press of a macro button on the toolbar.

 

[VoG]

Try this

Sub test()
Dim i As Long
With ActiveCell
    If .Value = "" Then
        .Value = Chr(10) & 1 & " - " & Environ("username") & " - " & Format(Date, "dd/mm/yyyy")
        .WrapText = True
    Else
        i = Len(.Value) - Len(Replace(.Value, Chr(10), "")) + 1
        .Value = .Value & Chr(10) & i & " - " & Environ("username") & " - " & Format(Date, "dd/mm/yyyy")
    End If
End With
End Sub

 

[marlowem]

VoG you are a star! Works like a dream. Whilst my hand is in the cookie jar.....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Is there any way to change the environment name to use say first & last rather than username (we have auto generated ID's which don't relate to much). Even better if it could concatenate them to form initials?!<o></o>
<o></o>
Thanks again<o></o>

 

[VoG]

This will use the user name that is entered in Tools > Options in Excel

Sub test()
Dim i As Long
With ActiveCell
    If .Value = "" Then
        .Value = Chr(10) & 1 & " - " & Application.UserName & " - " & Format(Date, "dd/mm/yyyy")
        .WrapText = True
    Else
        i = Len(.Value) - Len(Replace(.Value, Chr(10), "")) + 1
        .Value = .Value & Chr(10) & i & " - " & Application.UserName & " - " & Format(Date, "dd/mm/yyyy")
    End If
End With
End Sub