Time Stamp VBA - Need help please!

[kikie]

New to the group and thanks in advance for any assistance you can give
me. I'm somewhat a novice at this VBA stuff.

I have a spreadsheet that I currently have set up for users to change
the date in the footer when they make modifications. As you might
imagine, it doesn't get updated...everyone forgets - but it makes it
difficult to keep track of what version of the form you are looking at
and when someone last made changes to it.

So... I was able to track down some VBA from http://www.mcgimpsey.com/excel/timestamp.html
that is really close to what I'm looking for...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

However, if you make a change in the selected cells it will print the
date info in the cell next to it. That won't work for me. I would like
to be able to set this to where if a cell is changed in the
spreadsheet, the date information will change in a specific cell where
I will have a "Last modified on" label.

If someone knows how I might get this into my footer - that would be a
double bonus.

Also, a minor issue... I was able to get the vba script to run but I
wasn't sure how it is supposed to reallly be done. Don't laugh, but I
recorded a macro to go in to vba, run the routine, and go back in to
the spreadsheet. I know that can't possibly be the way to do it and
maybe I got something to run or work without doing it I just don't
know - so if someone could school me on it, that'd be great (I'm fine
doing it in Access, but can't figure this out to save my life)

Thank you so much for your assistance

 

[Bob Greenblatt]

New to the group and thanks in advance for any assistance you can give
me. I'm somewhat a novice at this VBA stuff.

I have a spreadsheet that I currently have set up for users to change
the date in the footer when they make modifications. As you might
imagine, it doesn't get updated...everyone forgets - but it makes it
difficult to keep track of what version of the form you are looking at
and when someone last made changes to it.

So... I was able to track down some VBA from
http://www.mcgimpsey.com/excel/timestamp.html
that is really close to what I'm looking for...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

However, if you make a change in the selected cells it will print the
date info in the cell next to it. That won't work for me. I would like
to be able to set this to where if a cell is changed in the
spreadsheet, the date information will change in a specific cell where
I will have a "Last modified on" label.

If someone knows how I might get this into my footer - that would be a
double bonus.

Also, a minor issue... I was able to get the vba script to run but I
wasn't sure how it is supposed to reallly be done. Don't laugh, but I
recorded a macro to go in to vba, run the routine, and go back in to
the spreadsheet. I know that can't possibly be the way to do it and
maybe I got something to run or work without doing it I just don't
know - so if someone could school me on it, that'd be great (I'm fine
doing it in Access, but can't figure this out to save my life)

Thank you so much for your assistance

The via is pretty simple. Use the following code instead, in the worksheet
code page:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("a1")=now 'Change this to reference the cell you want
pagesetup.leftfooter="last changed "&format(now," mm/dd/yyy hh:mm:ss")
End sub

 

[JE McGimpsey]

Also, a minor issue... I was able to get the vba script to run but I
wasn't sure how it is supposed to reallly be done. Don't laugh, but I
recorded a macro to go in to vba, run the routine, and go back in to
the spreadsheet. I know that can't possibly be the way to do it and
maybe I got something to run or work without doing it I just don't
know - so if someone could school me on it, that'd be great (I'm fine
doing it in Access, but can't figure this out to save my life)

This is an event macro, which means it's driven by an automatic event
(such as a change in a cell), not run manually. In order to run, it
needs to be placed in the worksheet's code module (right-click the
worksheet tab and choose View Code).