Need to prevent call Sub Worksheet_Activate() during macro.

  • Thread starter Thread starter HammerJoe
  • Start date Start date
H

HammerJoe

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
 
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
 
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.
 
Back
Top