Copy/Paste doesn't trigger macro
 

Not sure how you have implemented the trigger?

the Private Sub Worksheet_Change event macro will trip on Paste
--
Gary's Student


"Sinus Log" wrote:

I have a macro that is executed automatically when certain
values change in memory.
These values in memory are derived from cell values which
are read by udf's.
The macro sets the scales and other properties of scatter
charts in the workbook. I have inserted a Debug.Print
instruction for the purpose of testing.
I have also used the Code Cleaner add-in.

Everything is fine when I fill the cells manually, or when I
delete them, or when I copy/paste from an external program
(like notepad). Fine too when I mark and copy text in a cell
and then paste it in the appropriate cell.
But when I copy/paste from within Excel (either from
adjacent cells or from another workbook, and either with
Paste or with Paste Special / Values), the macro code isn't
executed. A Beep instruction inserted in the Change
Properties module isn't executed either.

Can anything be done about this ?
Thanks

Copy/Paste doesn't trigger macro
 

I have a macro that is executed automatically when certain
values change in memory.
These values in memory are derived from cell values which
are read by udf's.
The macro sets the scales and other properties of scatter
charts in the workbook. I have inserted a Debug.Print
instruction for the purpose of testing.
I have also used the Code Cleaner add-in.

Everything is fine when I fill the cells manually, or when I
delete them, or when I copy/paste from an external program
(like notepad). Fine too when I mark and copy text in a cell
and then paste it in the appropriate cell.
But when I copy/paste from within Excel (either from
adjacent cells or from another workbook, and either with
Paste or with Paste Special / Values), the macro code isn't
executed. A Beep instruction inserted in the Change
Properties module isn't executed either.

Can anything be done about this ?
Thanks

Copy/Paste doesn't trigger macro
 

Gary''s Student wrote:
Not sure how you have implemented the trigger?

the Private Sub Worksheet_Change event macro will trip on Paste

The macro's name is SetMinMax.
The function MiseEnMem sets the value of the variable
execSetMinMax to 0 or 1.
The code in the Change module:

Option Explicit
Private Sub Worksheet_Change(ByVal Region As Range)

Dim a As Long, b As Variant, critique As Long

If Not Intersect(Region, Range("a1:a1")) Is Nothing Then
critique = 1
enMemOptim_A = 0: enMemOptim_B = 0: enMemOptim_C = 0:
enMemOptim_D = 0
enMemAxeComp = 0: enMemAxesCompos = 0
End If
'(More regions...)

If critique = 1 Then
b = MiseEnMem("")
Application.Calculate
End If

If execSetMinMax = 1 Then SetMinMax
execSetMinMax = 0
End Sub

Copy/Paste doesn't trigger macro
 

Sinus Log wrote:
Gary''s Student wrote:
Not sure how you have implemented the trigger?

the Private Sub Worksheet_Change event macro will trip on Paste

The macro's name is SetMinMax.
The function MiseEnMem sets the value of the variable execSetMinMax to 0
or 1.
The code in the Change module:

Option Explicit
Private Sub Worksheet_Change(ByVal Region As Range)

Dim a As Long, b As Variant, critique As Long

If Not Intersect(Region, Range("a1:a1")) Is Nothing Then
critique = 1
enMemOptim_A = 0: enMemOptim_B = 0: enMemOptim_C = 0: enMemOptim_D = 0
enMemAxeComp = 0: enMemAxesCompos = 0
End If
'(More regions...)

If critique = 1 Then
b = MiseEnMem("")
Application.Calculate
End If

If execSetMinMax = 1 Then SetMinMax
execSetMinMax = 0
End Sub

I have created a small workbook to mimick the macro and
functions of my project, and there's no problem at all with
Paste. Now if I had a general idea of what is causing the
problem, I might be able to correct it. Could it be:
- too much load on the memory
- too many cells intercepted by the Sub Worksheet_Change
- the duration of the calculations
- path too long: worksheet - Worksheet_Change - udf -
Worksheet_Change - macro SetMinMax

Copy/Paste doesn't trigger macro
 

The event will not fire for a paste if any of the following are true
- Events disabled
- Macros disabled
- WS is protected and you are trying to paste into locked cells

Otherwise maybe your SetMinMax is not running because execSetMinMax < 1 ?

NickHK

"Sinus Log" wrote in message
...
Sinus Log wrote:
Gary''s Student wrote:
Not sure how you have implemented the trigger?

the Private Sub Worksheet_Change event macro will trip on Paste

The macro's name is SetMinMax.
The function MiseEnMem sets the value of the variable execSetMinMax to 0
or 1.
The code in the Change module:

Option Explicit
Private Sub Worksheet_Change(ByVal Region As Range)

Dim a As Long, b As Variant, critique As Long

If Not Intersect(Region, Range("a1:a1")) Is Nothing Then
critique = 1
enMemOptim_A = 0: enMemOptim_B = 0: enMemOptim_C = 0: enMemOptim_D =
0
enMemAxeComp = 0: enMemAxesCompos = 0
End If
'(More regions...)

If critique = 1 Then
b = MiseEnMem("")
Application.Calculate
End If

If execSetMinMax = 1 Then SetMinMax
execSetMinMax = 0
End Sub

I have created a small workbook to mimick the macro and
functions of my project, and there's no problem at all with
Paste. Now if I had a general idea of what is causing the
problem, I might be able to correct it. Could it be:
- too much load on the memory
- too many cells intercepted by the Sub Worksheet_Change
- the duration of the calculations
- path too long: worksheet - Worksheet_Change - udf -
Worksheet_Change - macro SetMinMax

Copy/Paste doesn't trigger macro
 

NickHK wrote:
The event will not fire for a paste if any of the following are true
- Events disabled
- Macros disabled
- WS is protected and you are trying to paste into locked cells

Otherwise maybe your SetMinMax is not running because execSetMinMax < 1 ?

NickHK



You're right about execSetMinMax < 1. I should have tested
it before.

I put debug.print statements at strategic points in the
code, and here is what I found out. When the data is entered
manually, the code runs in the main udf first (where
execSetMinMax is set to 1), then in the Worksheet_Change
module. But when the data is pasted instead, the code runs
in the Worksheet_Change module first, then in the main udf
(and execSetMinMax is set to 1, too late).

This seems to be a bug in Excel, not in my code. Am I right ?

Copy/Paste doesn't trigger macro
 

Excel's events fire in a well known order, for well known reasons. So I'm
pretty sure it's your code.
If you are changing execSetMinMax in your UDF AND in the _Change event that
will be caused by your UDF changing, that is something you have not planned
for.
Sounds like it should only be changed once per calculation.

NickHK

"Sinus Log" wrote in message
...
NickHK wrote:
The event will not fire for a paste if any of the following are true
- Events disabled
- Macros disabled
- WS is protected and you are trying to paste into locked cells

Otherwise maybe your SetMinMax is not running because execSetMinMax < 1
?

NickHK



You're right about execSetMinMax < 1. I should have tested
it before.

I put debug.print statements at strategic points in the
code, and here is what I found out. When the data is entered
manually, the code runs in the main udf first (where
execSetMinMax is set to 1), then in the Worksheet_Change
module. But when the data is pasted instead, the code runs
in the Worksheet_Change module first, then in the main udf
(and execSetMinMax is set to 1, too late).

This seems to be a bug in Excel, not in my code. Am I right ?