How to disable an other subroutine temporary?

[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. I guess it it the worksheet_activate that
trigger code that shouldn't.


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

 

[Vasant Nanavati]

Application.EnableEvents = False 'True

Be warned that event macros will not fire unless the property is explicitly
reset to True. You should also reset the property in your error handler in
case of an abnormal macro termination.

 

[Guest]

Thanks. About error handling, could you give an example how you mean about
how to deal with abnormal macro termination?

/Regards

 

[Vasant Nanavati]

Simplified version:

Sub Test()
Application.EnableEvents = False
On Error GoTo ErrorHandler
'Do whatever you want to do
Application.EnableEvents = True
Exit Sub
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Guest]

Thank you, enableevents=false did the trick

/Regards from Nobody