PC user tag with date/time stamp

[TobyCobbin]

I've got a "submit" button on my Excel spreadsheet and I would like to know what script/command to add to the assigned macro so that when the form is received by a second user (authoriser) it has the name and date/time tag of the person who submitted the form (form completer).

Is there anyway of looking at the user who is logged into the pc and "capturing" that information?

So once the form has been submitted, in the cell next to the submit button I need it to show something like: MR USER1 08/02/2014 10:08

Similarly, I'll need to do the same on a second submit button so the end view looks like:

MR USER1 08/02/2014 10:08
MRS USER2 09/02/2014 15:37

Any help/comments would be greatly received.

 

[Teeroy]

Try using the Environ Function. Here (Environment variables) is a list of the information that can be returned.

 

[TobyCobbin]

Teeroy - thanks for the tip and link. Much appreciated

 

[TobyCobbin]

Ok, so to get the date/time stamp I have entered:

Sub ShowTime()
Range("D10") = Now()
End Sub

What command do I need to enter to get the username details? I have looked on the (Environment variables) link and can see %USERNAME% which will return "the user name corresponding to the current account" but i'm not sure how to type it out!

Or is there another way to return the user name?

Toby

 

[Teeroy]

Hi Toby,

That link gave you the names but the % in the usage was for windows scripting. Just use:

environ("USERNAME")

Teeroy.

 

[TobyCobbin]

Thanks Teeroy - what is the exact script to put the username into cell D9? And how does it go with the time/date script? Can you write it out for me please?

 

[TobyCobbin]

*bump*

 

[Teeroy]

Try the following inside your sub:

Range("D9").value = environ("USERNAME")
Range("D11").value = environ("USERNAME") & " " & Now()