PDA

View Full Version : [SOLVED] Getting last modified date shown in footer and cell



kaoticat
08-09-2018, 06:37 AM
Hi there,

I'm an absolute beginner in regards to VBA and would appreciate it a lot if someone could help me solve my problem.

What I'm working with:
I use MS Office Professional Plus 2010 on a common W7 computer. My file is an Excel macro-enabled workbook (xlsm) with several worksheets.

What I want to achieve:
Each worksheet should show a certain date in its footer and in a certain cell. The date must be the time when the worksheet in question was last modified.

What I already achieved:
The first part is already done. Each worksheet shows the requested date in its footer.
This is the code that already works:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet.PageSetup
.RightFooter = "Status:" & " " & Format(Now, "dd/mm/yyyy hh:mm")
End With
End Sub

What my problem is now:
While the first part works just fine, I can't seem to get the second part working, which is letting the same date also show in a cell of the worksheet in question.
I've tried this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet.PageSetup
.RightFooter = "Status:" & " " & Format(Now, "dd/mm/yyyy hh:mm")
End With
With ActiveSheet
.Range("V5") = Format(Now, "dd/mm/yyyy hh:mm")
End With
End Sub
But it doesn't work. I see the date showing up in the relevant cell, but it flickers and Excel seems to crash. I also tried putting .Range("V5") = Format(Now, "dd/mm/yyyy hh:mm") in the same "With" as the footer code, but I ended up with errors.


I got the first part of the code from a German forum, but its users don't seem to like explaining stuff to absolute beginners so they can really understand what they're doing. So I'm turning to this forum.

I hope people here are willing to help me while keeping in mind that I know nothing about codes and the like. I'm very willing to learn, though, don't get me wrong. I just need easy explanations in words I understand. It would be so much appreciated if someone would take the time to look into my problem and help me get this stuff right.

If anything else is missing in order to solve my problem, please let me know!

Thanks in advance for any support.