How to disable a subrouting temporary from an other sub?

[Guest]

I am using "paste special" funktions to copy the format some cells have from
one sheet to antoher ( I copy the values as well as you see in code below)

but have event subs "Worksheet_Activacte" sub, "Worksheet_Change" sub and a
"Worksheet_Calculate" sub on that sheet as well. Becauser of the "paste
special" code it seems to trigger some of these and I need to temporary
disable the event subs, run the paste special and then turn them on again.

Can you please help me? I believe it should work if i can disable the event
subs first temporary in the code.


To copy format with "paste special" I use this code in Sheet105:


Private Sub Worksheet_Activate()

'Copy values from sheet104 ( Blad104 ) to 105

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rgn1 As Range
Dim rgn2 As Range

Set sh1 = Blad104 ' sheet104 in swedish
Set sh2 = Blad105 ' sheet105 in swedish

Set rng1 = sh2.Range(sh2.Cells(4, 1), sh2.Cells(96, 7))
Set rng2 = sh1.Range(sh1.Cells(4, 1), sh1.Cells(96, 7))

sh2.Range(sh2.Cells(4, 1), sh2.Cells(96, 2)).Value = sh1.Range(sh1.Cells(4,
1), sh1.Cells(96, 2)).Value


' Copy the format over:

rng1.Copy
Application.CutCopyMode = False
rng1.Copy
rng2.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False



/Thanksalot

 

[Jerry W. Lewis]

You could use a global variable as a flag to tell your event handlers to
exit without calculation. Also see help for "EnableCalculation Property"

Jerry

 

[Dave Peterson]

Surround your code that causes the event to fire with:

application.enableevents = false
'your .value = .value here
application.enableevents = true

application.enableevents = false
'your .pastespecial code here
application.enableevents = true

 

[~Alan]

put a ' in front of the line you dont want to fire
as in
' rng1.Copy
' Application.CutCopyMode = False
' rng1.Copy
' rng2.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
' SkipBlanks:=False, Transpose:=False

 

[Guest]

Thank you all of you,

Application.EnableEvents = False / True ... did the trick

/Regards