Auto Insert Date, Time and User Name of Last Modification

[aamartin]

Hello all,

I have a basic understanding of Excel and it's features and have modified very basic VBA code in the past. Okay, so I've built a work schedule encompassing a four week period with various employees and the workbook ("W1 - July 2015 Schedule") is shared amongst various supervisors. I've searched for a VBA code that will automatically insert the Date, Time and User Name of the excel user who last modified any part of the schedule in Cell "T56" in every sheet modified in this format "Revised: 06/30/2015 at 09:52 PM by John Doe" - John Doe being the registered Excel user not the computer user name. I'd like the results to be inserted into the specified cell so it can been seen by all users upon opening the file, rather than having to view the Print Preview. I've found similar code that appeared to do what I need, but haven't been able to make it work in my workbook.

Any help anyone can provide would be greatly appreciated!!

 

[mole999]

i think it is environ(username) for the user in VBA and if you just insert the value of Now() that can be formatted as date and time. I presume you will be doing that from VBA

whats your code, post it here

 

[aamartin]

i think it is environ(username) for the user in VBA and if you just insert the value of Now() that can be formatted as date and time. I presume you will be doing that from VBA

whats your code, post it here

Sub UserName()
Sheets("PP 17 - 18").Range("T56").Value = Environ("username")
End Sub


This is the code I have that inserts the data I need in a cell. I had other code, but was written to insert the data into a footer/header, which I don't want. There have been others that I trashed when I couldn't make it work. I think this code is similar to what you are suggesting, but is there a way to show the user name registered to excel, rather than the pc user name? Again, my ideal result is "Revised: 06/30/2015 at 09:52 PM by John Doe"

I think this code is specific to a worksheet. Will it work the same in a workbook? Does it matter much?

Thanks for you quick response and help!

 

[V_Malkoti]

To get username from Excel , use Application.UserName[

strMsg = "Revised: " & Date & " at " & Time & " by " & Application.UserName

 

[aamartin]

That worked nicely!

My current code is now:

Sub UserName()
Sheets("PP 17 - 18").Range("B56").Value = Application.UserName
End Sub

How do you suggest I get Date and Time of modification to get a response of "Revised: 06/30/2015 at 09:52 PM by John Doe"?

Thank you!!

 

[V_Malkoti]

strMsg = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName

 

[aamartin]

Thanks for your response.

How do I reference a specific cell to show the results? I understand the code and it looks like it will produce what I need, but making it work is way outside of my experience level.

 

[aamartin]

Thanks for your response.

How do I reference a specific cell to show the results? I understand the code and it looks like it will produce what I need, but making it work is way outside of my experience level.

strMsg = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName

Thanks for your response.

How do I reference a specific cell to show the results? I understand the code and it looks like it will produce what I need, but making it work is way outside of my experience level.

 

[V_Malkoti]

Refer to range in active sheet:

Activesheet.Range("T56") = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName

Since you want to enter this text in cell T56 of a sheet every time a user modifies that sheet, you can enter this code in SheetChange event.

1. Press Alt+F11
2. In Project Explorer pane on left, double click on ThisWorkbook node (Press Ctrl+R if Project Explorer is not shown)
3.Paste this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Range("T56") = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName
End Sub

 

[aamartin]

Refer to range in active sheet:

Activesheet.Range("T56") = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName

Since you want to enter this text in cell T56 of a sheet every time a user modifies that sheet, you can enter this code in SheetChange event.

1. Press Alt+F11
2. In Project Explorer pane on left, double click on ThisWorkbook node (Press Ctrl+R if Project Explorer is not shown)
3.Paste this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Range("T56") = "Revised: " & Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName
End Sub

I'm getting a Macro dialog box - any ideas?

I was going to insert screen shot, but apparently cant post those.