Need to prevent call Sub Worksheet_Activate() during macro.
 

I've run into a problem.

I have code that is supposed to be run everytime I activate the sheet,
which is located in the Private Sub Worksheet_Activate(),

Problem is that somewhere else in the code I have a routine to hide
all sheets except this particular one and Excel calls the Private Sub
Worksheet_Activate().
There are other instances as well that excel call for the sub when I
need it to to something on that sheet.
The problem is that Private Sub Worksheet_Activate() does some stuff
that I only want to be done when I select the sheet not when running
code.

Is there an easy way to make Excel ignore the sub unless I click on
the sheet tab to activate it?
Thanks

Need to prevent call Sub Worksheet_Activate() during macro.
 

Application.EnableEvents = False
'do stuff here
Application.EnableEvents = True

make sure to add error handling whenever you use this type of approach: if
an error occurs when EnableEvents is false then it will not revert back to
True when your code exits.

Tim



wrote in message
...
I've run into a problem.

I have code that is supposed to be run everytime I activate the sheet,
which is located in the Private Sub Worksheet_Activate(),

Problem is that somewhere else in the code I have a routine to hide
all sheets except this particular one and Excel calls the Private Sub
Worksheet_Activate().
There are other instances as well that excel call for the sub when I
need it to to something on that sheet.
The problem is that Private Sub Worksheet_Activate() does some stuff
that I only want to be done when I select the sheet not when running
code.

Is there an easy way to make Excel ignore the sub unless I click on
the sheet tab to activate it?
Thanks

Need to prevent call Sub Worksheet_Activate() during macro.
 

Take a look at Application.EnableEvents property :

Private Sub Worksheet_Activate()
Application.EnableEvents = False
' your code here ...
Application.EnableEvents = True
end sub

Regards,
J.M.

a écrit dans le message de news:
...
I've run into a problem.

I have code that is supposed to be run everytime I activate the sheet,
which is located in the Private Sub Worksheet_Activate(),

Problem is that somewhere else in the code I have a routine to hide
all sheets except this particular one and Excel calls the Private Sub
Worksheet_Activate().
There are other instances as well that excel call for the sub when I
need it to to something on that sheet.
The problem is that Private Sub Worksheet_Activate() does some stuff
that I only want to be done when I select the sheet not when running
code.

Is there an easy way to make Excel ignore the sub unless I click on
the sheet tab to activate it?
Thanks

Need to prevent call Sub Worksheet_Activate() during macro.
 

Thanks guys.