Hi all,

I have an .xls file in Excel 2002 format, in which it is used as a sort of
template in my group and then when new data is loaded onto it, it is saved
into under another name. It has two event calls in it:

SheetActivate()
SheetSelectionChange()

But I need to disable them load the new data in and then re-enable them. Is
there a way of doing this? Thanks for sharing your thoughts.


Ben

--

application.enableevents=false
'do stuff here
application.enableevents=true

that should do it....

--
www.alignment-systems.com


"Ben" wrote:

Hi all,

I have an .xls file in Excel 2002 format, in which it is used as a sort of
template in my group and then when new data is loaded onto it, it is saved
into under another name. It has two event calls in it:

SheetActivate()
SheetSelectionChange()

But I need to disable them load the new data in and then re-enable them. Is
there a way of doing this? Thanks for sharing your thoughts.


Ben

--

As has been pointed out, you should use EnableEvents. However, you
should also be aware of its limitations and its persistent nature. From
Monitoring events
http://www.tushar-mehta.com/excel/vb...L%20events.htm


An Excel specific trap: the EnableEvents property

The first problem with the EnableEvents property is that it applies only
to the Excel application. There are many objects that a developer uses
within the Excel environment that are not actually part of Excel object
heirarchy. Event associated with those objects will continue to be
raised. One of the most common objects is a userform! The userform is
part of the Office library and setting Excel=3Fs EnableEvents property to
False will have no effect on events raised by a userform or by objects
inside a userform. As it turns out there is no built-in method to block
userform events. For a workaround see chapter xx.

The other problem with the EnableEvents property is that it is
persistent in that it is not reset to True when user written code stops
executing. By contrast, if one programmatically set ScreenUpdating to
False, Excel will reset it to True when the VBA code stops executing.
Since this doesn=3Ft happen in the case of EnableEvents, it is imperative
that the developer reset it to True. This must happen with no ifs or
buts. The easiest way to do so is to always use an error trap with the
EnableEvents property. There are a few different ways to do this and
Code Sample 16 illustrates one.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrXIT
Application.EnableEvents = False
With Target.Parent.Cells(1, 1)
.Value = .Value + 1
End With
ErrXIT:
Application.EnableEvents = True
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article ,
says...
Hi all,

I have an .xls file in Excel 2002 format, in which it is used as a sort of
template in my group and then when new data is loaded onto it, it is saved
into under another name. It has two event calls in it:

SheetActivate()
SheetSelectionChange()

But I need to disable them load the new data in and then re-enable them. Is
there a way of doing this? Thanks for sharing your thoughts.


Ben

Hi Tushar,

"By contrast, if one programmatically set ScreenUpdating to
False, Excel will reset it to True when the VBA code stops executing. "

This is true in old versions of Excel.

In Excel 2002 and above it's not true (I think the switch happened in 2000 -
but I do not have a copy running on a box available to me right now to
check). Time for an edit????




--
www.alignment-systems.com


"Tushar Mehta" wrote:

As has been pointed out, you should use EnableEvents. However, you
should also be aware of its limitations and its persistent nature. From
Monitoring events
http://www.tushar-mehta.com/excel/vb...L%20events.htm


An Excel specific trap: the EnableEvents property

The first problem with the EnableEvents property is that it applies only
to the Excel application. There are many objects that a developer uses
within the Excel environment that are not actually part of Excel object
heirarchy. Event associated with those objects will continue to be
raised. One of the most common objects is a userform! The userform is
part of the Office library and setting Excel=3Fs EnableEvents property to
False will have no effect on events raised by a userform or by objects
inside a userform. As it turns out there is no built-in method to block
userform events. For a workaround see chapter xx.

The other problem with the EnableEvents property is that it is
persistent in that it is not reset to True when user written code stops
executing. By contrast, if one programmatically set ScreenUpdating to
False, Excel will reset it to True when the VBA code stops executing.
Since this doesn=3Ft happen in the case of EnableEvents, it is imperative
that the developer reset it to True. This must happen with no ifs or
buts. The easiest way to do so is to always use an error trap with the
EnableEvents property. There are a few different ways to do this and
Code Sample 16 illustrates one.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrXIT
Application.EnableEvents = False
With Target.Parent.Cells(1, 1)
.Value = .Value + 1
End With
ErrXIT:
Application.EnableEvents = True
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article ,
says...
Hi all,

I have an .xls file in Excel 2002 format, in which it is used as a sort of
template in my group and then when new data is loaded onto it, it is saved
into under another name. It has two event calls in it:

SheetActivate()
SheetSelectionChange()

But I need to disable them load the new data in and then re-enable them. Is
there a way of doing this? Thanks for sharing your thoughts.


Ben