Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal TargetAs Range) event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal Target
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 "gordom" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal TargetAs Range) event
gordom wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal Target
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 -- RyGuy "gordom" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal Target
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "ryguy7272" wrote in message ... 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 -- RyGuy "gordom" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal TargetAs Range) event
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a delay to Private Sub Worksheet_Change(ByVal Target As Range) event
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "gordom" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Worksheet Functions | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming |