|  | VBA Time Stamp... I'm close, but need help | | |
| | | kikie |  |
|  Posted: Thu Jul 24, 2008 9:19 pm Post subject: VBA Time Stamp... I'm close, but need help |  |
| |  | |
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 LINK
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 |
| |
| | | Gord Dibben | |
| | Posted: Thu Jul 24, 2008 9:19 pm Post subject: Re: VBA Time Stamp... I'm close, but need help | |
| | | |
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 mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Gord Dibben MS Excel MVP
On Thu, 24 Jul 2008 14:19:26 -0700 (PDT), kikie <kkasner@gmail.com> wrote:
| Quote: | 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 LINK
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 |
|
| |
| | | kikie | |
| | Posted: Fri Jul 25, 2008 3:42 pm Post subject: Re: VBA Time Stamp... Found the solution! | |
| | | |
Found It!!! LINK
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 |
| |
| | | Gord Dibben | |
| | Posted: Fri Jul 25, 2008 6:33 pm Post subject: Re: VBA Time Stamp... Found the solution! | |
| | | |
Good to hear you're sorted.
Gord
On Fri, 25 Jul 2008 08:42:57 -0700 (PDT), kikie <kkasner@gmail.com> wrote:
| Quote: | Found It!!! LINK
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 |
|
| |
| | | kikie | |
| | Posted: Mon Jul 28, 2008 8:41 pm Post subject: Re: VBA Time Stamp... Designate different location for diffe | |
| | | |
On Jul 25, 1:33 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
| Quote: | Good to hear you're sorted.
Gord
On Fri, 25 Jul 2008 08:42:57 -0700 (PDT), kikie <kkas...@gmail.com> wrote:
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- Hide quoted text -
- Show quoted text -
|
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
| Quote: |
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("A7").Value = "vbGeneralDate"
Range("B7").Value = FormatDateTime(Now, vbGeneralDate)
Columns("A:B").EntireColumn.AutoFit
Range("A1").Select
End Sub
|
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 |
| |
| | | kikie | |
| | Posted: Wed Aug 06, 2008 4:03 pm Post subject: Re: VBA Time Stamp... Designate different location for diffe | |
| | | |
On Jul 28, 1:41 pm, kikie <kkas...@gmail.com> wrote:
| Quote: | On Jul 25, 1:33 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
Good to hear you're sorted.
Gord
On Fri, 25 Jul 2008 08:42:57 -0700 (PDT), kikie <kkas...@gmail.com> wrote:
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 atimeusing 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 atimeusing thetimeformat specified in your
computer's regional settings.
Range("A5").Value = "vbLongTime"
Range("B5").Value = FormatDateTime(Now, vbLongTime)
'Display a date and/ortime. If there is a date part, display it
as a short date. _
If there Is atimepart, display it As a Longtime. 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- Hide quoted text -
- Show quoted text -
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
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
> Range("A7").Value = "vbGeneralDate"
Range("B7").Value = FormatDateTime(Now, vbGeneralDate)
Columns("A:B").EntireColumn.AutoFit
Range("A1").Select
End Sub
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 - |
|
| |
| | | kikie | |
| | Posted: Wed Aug 06, 2008 4:24 pm Post subject: Re: VBA Time Stamp... Designate different location for diffe | |
Ok... I found it but I have one more issue to iron out.
| Quote: | I have 4 worksheets and each one of them has a different number of
rows.
|
| Quote: | 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
|
| Quote: | 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)
|
| Quote: | The code again is.
|
| Quote: | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
|
| Quote: | Range("A7").Value = "vbGeneralDate"
|
| Quote: | Range("B7").Value = FormatDateTime(Now, vbGeneralDate)
|
| Quote: | Columns("A:B").EntireColumn.AutoFit
Range("A1").Select
End Sub
|
| Quote: | 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 |
| |
| | | Guest | |
| | Posted: Sun Aug 31, 2008 8:54 pm Post subject: Great - but can we do it twice?! | |
Hi,
Your code below is perfect for me:
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 mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
....but I want to be able to repeat this for different cell ranges (e.g. Target, Me.Range("N1:N10") with the next timestamp showing in "Q1" - I've tried but get duplicate declaration errors (because I've no clue how to repeat VBA functions!). Sorry that this is probably a basic q, but pleasecould you help?
Thanks
A |
| |
| | | Guest | |
| | Posted: Sun Aug 31, 2008 8:54 pm Post subject: VBA Time Stamp - can we do it twice? | |
Hi,
Your code below is perfect for me:
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 mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
....but I want to be able to repeat this for different cell ranges (e.g. Target, Me.Range("N1:N10") with the next timestamp showing in "Q1" - I've tried but get duplicate declaration errors (because I've no clue how to repeat VBA functions!). Sorry that this is probably a basic q, but pleasecould you help?
Thanks
A |
| |
| | | AltaEgo | |
| | Posted: Sun Aug 31, 2008 9:20 pm Post subject: Re: Great - but can we do it twice?! | |
| | | |
"Andrew Faulding" wrote in message
news:200883118542afaulding@ntlworld.com...
| Quote: | Hi,
Your code below is perfect for me:
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 mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
...but I want to be able to repeat this for different cell ranges (e.g.
Target, Me.Range("N1:N10") with the next timestamp showing in "Q1" - I've
tried but get duplicate declaration errors (because I've no clue how to
repeat VBA functions!). Sorry that this is probably a basic q, but
pleasecould you help?
Thanks
A
|
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 mmm yyyy hh:mm:ss")
End If
End With
End If
If Not Intersect(Target, Me.Range("N1:N10")) Is Nothing Then
With Target
If .Value <> "" Then
Range("Q1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub |
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
