How to set up a delay to Private Sub Worksheet_Change(ByVal TargetAs Range) event

[gordom]

Hi,
I have a sheet with a pivot table. The data from pivot table report are
modified and imported to another range of cells. The layout of this
cells is changed by macro. If I triggered the macro manually it works
fine. The problem starts if I want to execute the macro automatically
when one of the cells is changed (I use "Private Sub
Worksheet_Change(ByVal Target As Range)" event). Unfortunately macro
corrupts data in that case. I suppose that macro starts modifying the
layout before all data are fully imported from the pivot. I tried to set
up some delay to the event but couldn't figure out how to do so. Could
you please help me? Thanks in advance.
gordom



my code syntax

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$245" Then


(my macro code)


End If
End Sub

 

[JLGWhiz]

To increase or decrease the time of delay, change the s = Timer + # line.
The # is measured in seconds, currently at .5 (1/2) second.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$245" Then
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop

(my macro code)


End If
End Sub

 

[Dave Morison]

Hi,
I have a sheet with a pivot table. The data from pivot table report are
modified and imported to another range of cells. The layout of this
cells is changed by macro. If I triggered the macro manually it works
fine. The problem starts if I want to execute the macro automatically
when one of the cells is changed (I use "Private Sub
Worksheet_Change(ByVal Target As Range)" event). Unfortunately macro
corrupts data in that case. I suppose that macro starts modifying the
layout before all data are fully imported from the pivot. I tried to set
up some delay to the event but couldn't figure out how to do so. Could
you please help me? Thanks in advance.
gordom



my code syntax

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$245" Then


(my macro code)


End If
End Sub

Try,
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$245" Then

application.enableevents=false
(my macro code)

application.enableevents=true
End If
End Sub

 

[ryguy7272]

Something like this:
Application.Wait Now + TimeValue("00:00:10")

So, it may evolve into:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$245" Then

Application.Wait Now + TimeValue("00:00:10")

(my macro code)


End If
End Sub

 

[Jon Peltier]

If you use Application.Wait, you are liable to wait, while nothing else
happens, including updating of the data.

The Do While with DoEvents is a good approach. Another is to put the code
you want to run into another routine, and use Application.OnTime to invoke
it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$245" Then
Application.OnTime Now + TimeValue("00:00:10"), "CodeToRunSoon"
End If
End Sub

In a regular module:

Sub CodeToRunSoon()
' your code
End Sub


- Jon

 

[gordom]

Thanks guys for your help. I tested the delay codes and
they work fine. Nevertheless my problem is not solved
yet. There is some more issue that didn't notice before.
The Target.Address ("$J$245") refers to the cell that
contains formula. However the value in the cell changes
the formula stays the same. It means that there is no
trigger for the "Private Sub Worksheet_Change(ByVal
Target As Range)" event. The possible values of the
$J$245 cell are unknown. Could you please help me with
this matter also (sorry but I'm very much a novice in
VBA). Thanks,
gordom

 

[Jon Peltier]

Ah, J245 isn't changing, because the formula in the cell is the same. There
are a couple of ways to capture changes to the value calculated by the
formula in J245.

You could use the precedents of J245 as the target cells of interest.

You could save the value of J245 in another cell (say, K245) and use the
Worksheet_Calculate event as your trigger instead. If J245 = K245, there's
no change, so exit. Otherwise, put the new value of J245 into K245, then
perform the action you want to happen when J245 changes in value.

- Jon