Record time cell changes when cell is changed by formula/database refresh

[phildetwei]

Hello all.

I use the code below to record the time a cell value changes. This works good so long as the cell was changed by direct input. I have a case where it would valuable to be able to automatically record the time/date a cell was modified for cells that are changing, but changing only by way of a refresh from a query on an Access database and not by having data directly inputed into them.

If somebody could start me off in the right direction I think I could get something working but I am not sure if I can use something similar to my existing code or if I need to start using a totally different method to accomplish this (I suspect the latter).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Any help is much appreciated, THANK YOU!

 

[Jon von der Heyden]

Hi Phil

Is there only the one query table that you need to trap events for, or more than one?

For starters, read this.

 

[phildetwei]

I think that is what I am missing, thanks for the quick reply.

I only have one table currently as I am building a prototype/test version of something I may wish to use on a larger scale. Just kind of seeing what will be possible/practical at this point. Are there implications of having more than one table?

Thanks.

 

[phildetwei]

I see that this article is referenced quite a bit but I guess I am in over my head this time. When I paste the following into a new module as outlined in step 7,

Dim X As New Class1

Sub Initialize_It()
Set X.qt = Thisworkbook.Sheets(1).QueryTables(1)
End Sub

It returns the "subscript out of range" error when run and I am not sure how to proceed as i really don't have to good a handle on how this is supposed to work.

Thanks again for the help, I will keep trying to learn more so maybe I can do it.

 

[phildetwei]

This seems to make the Initialize_It() sub run properly in case anyone else is following along:

Sub Initialize_It()
Set X.qt = Worksheets("ETC_ALL").ListObjects(1).QueryTable
End Sub

Apparently some issue with 2007 redefining the QueryTable object as a ListObject. Not sure though. I should have mentioned I was using 2007.

Chipping away, learning as I go, thanks for the help!

Phil

 

[phildetwei]

I appreciate the help on this so far. I do seem to be stuck regarding where to place the code so will be raised by the AfterRefresh event. I followed the directions on the MS tuturial so I think I am close.

I have been trying to simply bring up a msgbox on the refresh event but I can't seem to figure even that out. If someone would be kind enough to show me that part, I believe I could figure the rest out from there.

I have a Class Module named ClsModQT with this in it:

Private Sub AfterRefresh(ByVal Success As Boolean)
If Success Then
MsgBox "success"
Else
MsgBox "Query has failed or has been canceled"
End If
End Sub

I have a module named InitRefresh with this in it:

Dim X As New ClsModQT

Sub Initialize_It()
Set X.qt = Worksheets("ETC_ALL").ListObjects(1).QueryTable
MsgBox "Initialize_It ran"
End Sub

Where would I put the code to insert the date/time based on the target range of cells being changed? I apologize if this is a dumb question.


Thanks again!

 

[Jon von der Heyden]

Hi again

In class module called clsModQT

[COLOR=Red]Public WithEvents qrt As QueryTable[/COLOR]

Private Sub [COLOR=Red]qrt_[/COLOR]AfterRefresh(ByVal Success As Boolean)
    If Success Then
        MsgBox "success"
    Else
        MsgBox "Query has failed or has been canceled"
    End If
End Sub

In a standard module:

Dim X As New clsModQT

Sub Initialize_It()
    Set X.qrt = Worksheets("ETC_ALL").ListObjects(1).QueryTable
    MsgBox "Initialize_It ran"
End Sub

You need to run Initialize_It before you create an instance of the class. You might want to run this in the workbook open event. You should also probably have a terminate procedure that you call.

Sub Terminate_It()
    Set X = Nothing
End Sub

Where would I put the code to insert the date/time based on the target range of cells being changed? I apologize if this is a dumb question.

Can you describe what results you are looking for? Do you mean you want a table that lists every cell that has changed? That's quite a task. I think a sample of the data might help too. See link in my signature (posting guidelines) to see how you can show us a sample of your data.

 

[Jon von der Heyden]

Are there implications of having more than one table?

If you have multiple tables, this will only fire up for the assigned query table. Do you want to fire the event for multiple tables?

 

[phildetwei]

Hello again.

I have learned so much from your post and the information you directed me to.

As it turns out, I went a different direction as I am not certain what I originally wanted was really possible. I am now using a circular formula to advance the number in each cell corresponding to the condition I am recording being true.

Something like:

=IF(L2<>0,O2+1,0) where L2 equals 0 until a text value is placed via the refresh. Each additional refresh advances the value in O2 until the condition is no longer true, effectively resetting the process.

I have a series of columns like this that measure various aspects of the data table, which is constantly changing.

Thanks again for all the info, I really appreciate it!

Phil