How to disable an other subroutine temporary?
 

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

How to disable an other subroutine temporary?
 

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.

--

Vasant

wrote in message
...
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

How to disable an other subroutine temporary?
 

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

/Regards


"Vasant Nanavati" <vasantn *AT* aol *DOT* com skrev i meddelandet
...
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.

--

Vasant

wrote in message
...
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

How to disable an other subroutine temporary?
 

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

--

Vasant



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

/Regards


"Vasant Nanavati" <vasantn *AT* aol *DOT* com skrev i meddelandet
...
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.

--

Vasant

wrote in message
...
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

How to disable an other subroutine temporary?
 

Thank you, enableevents=false did the trick

/Regards from Nobody


"Vasant Nanavati" <vasantn *AT* aol *DOT* com skrev i meddelandet
...
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

--

Vasant



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

/Regards


"Vasant Nanavati" <vasantn *AT* aol *DOT* com skrev i meddelandet
...
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.

--

Vasant

wrote in message
...
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