time stamp 2

[natedeia]

I have a spreadsheet that gives a timestamp when the sheet is updated. There is a button on the first worksheet that queries a DB and populates the data in the second worksheet. If I put this code on the data worksheet then it becomes real slow due to updating the timestamp when each cell is updated or being populated. So how can I get this to give me an updated time without updating when each cell populates
Secondly, if I can not find a way around that, how can I have this code triggered when a button is pressed??




Private Sub workbook_Beforesave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If Not ws.Range("a1") = ws.Range("A1") Then
ws.Range("A1") = "Last Run: " & Now
ws.Range("A1") = "Last Run: " & Now
Else: End If
Next ws
' Use "Now" for date and Time, use "Date" for just date
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range("c1") = "Last Run" & Now
End Sub

 

[natedeia]

should i reword this one? sorry

 

[grrr72]

I use the following, set up with a keyboard shortcut of Ctrl & t to put a timestamp in the activecell of a workbook.
You could modify as required, and maybe add a few lines in to save the workbook afterward.

Sub snapshot()
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=now()"

Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


Hope that helps

Nic

 

[TonyJollans]

Hi natedeia,

Your performance problem is due to changing the worksheet in the worksheet change event which invokes the event which changes the worksheet which invokes etc., etc. I think the only thing which stops it looping forever is a stack limit.

If you're going to do this you should disable events first, ..

[blue]Private Sub Worksheet_Change(ByVal Target As Range)
[red]Application.EnableEvents = False[/red]
Range("c1") = "Last Run" & Now
[red]Application.EnableEvents = True[/red]
End Sub[/blue]

I'm not sure exactly what you are doing but if you are updating the worksheet from code you can control what code and or what events are run from within that code and it is probably more efficient not to invoke the change event at all but simply to update the cell when you are ready.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[natedeia]

cool guys, thanks for the reply, Tony I think your approach is closer to what i will need, i will try it asap! and let you know

 

[natedeia]

ahh ha! thank you very much tonyj, that is why they pay you the big bucks!

 

[TonyJollans]

Happy to help. Thanks for the star!

that is why they pay you the big bucks!

I wish they did

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[xlbo]

We can only dream eh Tony

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[natedeia]

Hey Mr. Big Bucks, it seems to be doing it still, maybe I can make it give a timestamp just if one cell is updated, like A2 or something.
But your code change did seem to speed up the cell updates.

 

[TonyJollans]

Still doing what exactly?

What process do you have currently updating the worksheet. And what wants updating witha timestamp and when? Do you actually need the cell updating all the time or only, perhaps, on workbook close (or save)? Incidentally what is your before save routine trying to do?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Bowers74]

Change:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If Not ws.Range("a1") = ws.Range("A1") Then
ws.Range("A1") = "Last Run: " & Now
ws.Range("A1") = "Last Run: " & Now
Else: End If
Next ws
' Use "Now" for date and Time, use "Date" for just date
End Sub

to . . .

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.[A1] = "Last Run: " & Now
Next ws
End Sub

Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Bowers74]

oh, yeah . . .

Lose the Worksheet_Change events!

;-)

Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[natedeia]

tonyj, sorry for little info, spreadsheet with first worksheet has criteria i can query a database for information (can fill in specific criteria in a few rows), then it dumps the data onto worksheet 2, worksheet 3 is the space where the errors get sent to if any, has probs with ODBC errors on occasion when a driver needs to be updated or something,
so the update i want to go in cell c3 on worksheet 2 cause i copy that into another spreadsheet to do other stuff to it, but want the time stamp at the top of each query, nothing fancy,
thanks Bower, i am going to check that out in morning, appreciate everything