Excel sheet update problem

[Audissimo]

Hi everyone!

I have a following problem. I have an Excel sheet, which uses a auto web query feature and it supposed to update cell J17 value. The query works well but the problem is that I want to add to column A, in the next empty cell, the updated value, from cell J17, by the web query. Well, it works well, if I update the cell J17 manually, but it won't work if update is done by web query. In addition, my code adds a date and time to columns B and C if colunm A has a data.

I created a code below:

Private Sub Cell_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2 :A60000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
With Target(1, 3)
.Value = Now
.EntireColumn.AutoFit
End With
End If

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = "$J$17" Then
'Ensure target is a number
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target does not _
put the code into a loop.
Application.EnableEvents = False
Target.Copy
'Turn events back on
Application.EnableEvents = True
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection = Target

'Allow run time errors again
On Error GoTo 0
End If
End If
End Sub

-JK

 

[combo]

Guess that you do not expect Cell_Change procedure to response to events and use other event handler to call it and pass Target parameter.
It is possible to directly response to web query data update event only, you need to declare Withevents variable (worksheet module) and assign to it QueryTable variable in workbook open event for instance:

' worksheet module code
Public WithEvents qtWeb As QueryTable

Private Sub qtWeb_AfterRefresh(ByVal Success As Boolean)
If Success Then
    ' update sheet
Else
    ' update error message
End If
End Sub

combo

 

[xlbo]

queries don't tend to set off the change event - you may be better off using the CACLULATE event.

Set a cell with a simple formula such as

=J17

when J17 is updated by the query, its value will change and will therefore set off the calculate event - you can then use this in the same way as the change event

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question