Mac Forum / Applications / Excel / July 2008

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