VBA Time Stamp... I'm close, but need help

  • Thread starter Thread starter kikie
  • Start date Start date
K

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
 
You want the last modified date in the footer only?

Last modified date is last saved date which you can get in a custom footer
without any code.

You want the modified date in a cell?

Which cell?  How about P1?

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
        With Target
            If .Value <> "" Then
                Range("P1").Value = Format(Now, "dd mmmyyyy hh:mm:ss")
            End If
        End With
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

Gord Dibben  MSExcelMVP












- Show quoted text -
Click to expand...

Now... If I can get the vba to work, it might be better (but I'm not
having luck and I'm sure that comes from my inexperience)

But, I would be happy with the 'last saved date' in the footer - if it
doesn't count the 'auto save' as a save.I don't know how to get the
'last saved' into my footer - all I see is the date stamp which
changes whenever you open the file.

I guess the problem is that we transfer information from an oracle run
report into this excel spreadsheet. Sometimes we go in to the
document, make changes and print, other times we just go in and print
and need to know the date that it was last changed so we know which
Oracle report to refer to. Are we doing double work? Yep. But one
change at a time, right?

I guess this contradicts my own request/purpose, but is there a way to
have the "last modified" date update as I'm closing the file if
changes have been made? Instead of the date stamp changing with every
cell I modify? I think I'm asking for a lot here, but if someone has
any input to give on this... well, it'd be appreciated.

My thanks to Gord Dibben MSExcelMVP for his assistance on this :)
 
Found It!!! http://www.vbaexpress.com/kb/getarticle.php?kb_id=538

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

'Display a date using the short date format specified in your
computer's _
regional settings.
Range("A1").Value = "vbShortDate"
Range("B1").Value = FormatDateTime(Now, vbShortDate)

'Display a time using the 24-hour format (hh:mm).
Range("A2").Value = "vbShortTime"
Range("B2").Value = FormatDateTime(Now, vbShortTime)

'Display a date using the long date format specified in your
computer's regional _
settings.
Range("A4").Value = "vbLongDate"
Range("B4").Value = FormatDateTime(Now, vbLongDate)

'Display a time using the time format specified in your
computer's regional settings.
Range("A5").Value = "vbLongTime"
Range("B5").Value = FormatDateTime(Now, vbLongTime)

'Display a date and/or time. If there is a date part, display it
as a short date. _
If there Is a time part, display it As a Long time. If present,
both parts are displayed.
Range("A7").Value = "vbGeneralDate"
Range("B7").Value = FormatDateTime(Now, vbGeneralDate)

Columns("A:B").EntireColumn.AutoFit
Range("A1").Select
End Sub


How to use:

From Excel go to Tools-Macros-Visual Basic Editor. (ALT+F11)
In the Project Explorer (CTRL+R), click on the ThisWorkbook object
Paste the code above into the code pane
Close the Visual Basic Editor (File-Close)
Save your file and close it.



Thank goodness they show "how to use" as well for people like me who
are clueless... Worked like a charm
 
Good to hear you're sorted.

Gord
















- Show quoted text -
Click to expand...

Ok... I found it but I have one more issue to iron out.

I have 4 worksheets and each one of them has a different number of
rows.

I want the date and time stamp to appear at a certain position for
each of the sheets. For example one is A8 B8
Another is
A 107 B 107

I need it to call the location for each worksheet (I don't care if
they all update at once, I just need them in different places for
formatting sake)

The code again is.

Option Explicit
Range("A7").Value = "vbGeneralDate"
Click to expand...


I hope my request makes sense. I'm pretty sure this is something that
can be done,; I'm just too green to figure out how.

Thanks in advance

-K
 
Ok... I found it but I have one more issue to iron out.

I have 4 worksheets and each one of them has a different number of
rows.

I want the date andtimestampto appear at a certain position for
each of the sheets. For example one is A8 B8
Another is
A 107 B 107

I need it to call the location for each worksheet (I don't care if
they all update at once, I just need them in different places for
formatting sake)

The code again is.

Option Explicit


 >    Range("A7").Value = "vbGeneralDate"


I hope my request makes sense. I'm pretty sure this is something that
can be done,; I'm just too green to figure out how.

Thanks in advance

-K- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top