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!!

 

[Michael M]

As mole suggested wouldn't now be easier

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Range("T56") = "Revised at: " & Now() & " by " & Application.UserName
End Sub

 

[aamartin]

As mole suggested wouldn't now be easier

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Range("T56") = "Revised at: " & Now() & " by " & Application.UserName
End Sub

Thanks Michael..

It looks like that will produce the result I want - however, when I tried to run the code, I received a Macro dialog box. I posted it to the Workbook node and ran it and then I tried the sheet and ran it; got the same result on both attempts. Have any idea why the macro dialog is popping up or how to deal with that?

 

[Michael M]

It's definitely in "This Workbook"...NOT....the worksheet module.
It works fine for me !!

 

[V_Malkoti]

This is an event code - it will automatically run when you modify any cell values. You can't run it with F8 or F5.

 

[Michael M]

Hey V
Yeah gotcha, I was thinking of the OP having an Error dialog box...

 

[aamartin]

This is an event code - it will automatically run when you modify any cell values. You can't run it with F8 or F5.

Malkoti,

Thank you very much.. It is working now, just as I wanted it!! But, I am receiving a "Run-time error '28' Out of stack space when it runs.. Any idea?

When I try to debug, it leads me to this code in my Worksheet node.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rngCell As Range

If Intersect(Target, Range("E18:AF48")) Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each rngCell In Target.Cells
rngCell = UCase(rngCell)
Next
Application.EnableEvents = True

End Sub

This portion of the second line is highlighted "If Intersect(Target, Range("E18:AF48")) Is Nothing Then"

 

[Michael M]

It would have been a lot simpler if you had advised of this other code at the start of the thread
Remove the codes we have been discussing and simply use this one !

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngCell As Range
If Intersect(Target, Range("E18:AF48")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rngCell In Target.Cells
rngCell = UCase(rngCell)
Next
Range("T56").value = "Revised at: " & Now() & " by " & Application.UserName
Application.EnableEvents = True
End Sub

 

[aamartin]

It would have been a lot simpler if you had advised of this other code at the start of the thread
Remove the codes we have been discussing and simply use this one !

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngCell As Range
If Intersect(Target, Range("E18:AF48")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rngCell In Target.Cells
rngCell = UCase(rngCell)
Next
Range("T56").value = "Revised at: " & Now() & " by " & Application.UserName
Application.EnableEvents = True
End Sub

So sorry! My experience with VBA is very limited and didn't realize the other code would affect this new one - thought they ran independent of each other. But a big thank you to you and everyone who helped me with this! This is my first time reaching out to a forum for help and it's been a great experience.

 

[Michael M]

The problem you had was that the worksheet_change event was firing the workbook_Sheetchange event, which in turn was starting the worksheet_ change event again......thus creating an on going loop of events.

For future reference, try to provide all information about your workbook/sheet when posting, it makes it far easier to resolve.....and V_Malkoti would have given you a one liner right at the beginning of the thread.....would have saved you heaps of time AND confusion
Glad you sorted the problem...

 

[df06575]

Hi aamartin,

Do you still have the code that inserts the data into a footer? That is what I am trying to do and can't find a good example of how to accomplish it.

Thanks!