updates too much 1

[natedeia]

I am using this code to update a sheet once excel is done querying a DB, it gets dumped into the second sheet called Data and there it gives me a timestamp in cell C1. Like it alot but when there is alot of data it will sit there update each cell and update the time stamp. can someone fix it where when just A2 gets updated to perform this function??


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
Range("A1") = "Last Revision on: " & 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)
Application.EnableEvents = False
Range("c1") = "Last Run" & Now
Application.EnableEvents = True
End Sub

 

[TonyJollans]

Hi natedeia,

You can add a check at the start of the Worksheet Change Event so that it only updates when a particular cell changes ..

[blue]Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
    [green]' Rest of your code follows here[/green]

End Sub[/blue]

.. but the procedure still runs and may take some time.

How are you driving your database query? What else do you do after the query? Could you put the timestamping code somewhere other than this event?

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]

really, thanks....someone pointed that out the other day and could not find any other time to figure that one out.
it queries via excel w/vba. if the query was kinda big then you could watch it begin, cell by cell by cell. i wanted that timestamp though, no worries now.
will have to try it out at work tomorrow.