Don't re-run macro for five seconds...?

[Chris J Denver]

Hi newsgroup,

I have a macro that runs some code when a tab is clicked on. Now the
problem is that this macro changes tabs and does some other stuff
there and then returns to the initial tab. Which would trigger the
same macro again.

To prevent this I would ideally have a timer in the macro to prevent
it from looping in that it should not do the same macro twice within 5
seconds (or something along those lines).

Is this possible? And if so, how?

Many thanks,

Chris

 

[Gord Dibben]

I assume worksheet_activate event taking place.

Post the code.

Very rarely you would need to switch tabs to do some other stuff.

You may also disable events to prevent the looping.

On Error GoTo wsexit
Application.EnableEvents = False
switch sheets
do some other stuff
return to original sheet
wsexit:
Application.EnableEvents = True


Gord

 

[Chris J Denver]

Hi Gord,

Yes this is triggered by Worksheet_Activate

what it then does is call a number of other macros on some other
sheets, and some of them require the tabs to be changed:

Sub Worksheet_Activate()

Application.ScreenUpdating = False
Call Sheet17.macrosort
Call Sheet35.macrofill
Call Sheet64.macrosort
ActiveWorkbook.Worksheets("Printsheet").Select
Range("A1").Select
Application.ScreenUpdating = True

Now i guess i could rewrite all those macros in a way that you
actually don't need to be on the respective tabs, but this would be
quite cumbersome...

I will give the disable events thing a go, thanks for that.

Best,

Chris

 

[GS]

Chris J Denver formulated on Tuesday :

Now i guess i could rewrite all those macros in a way that you
actually don't need to be on the respective tabs, but this would be
quite cumbersome...

It's worth the effort because, as Gord states, rarely is it necessary
to make sheets active to work on them, AND it's more efficient to write
code that reduces ALL unnecessary action whether behind the scenes or
not.

If what you mean by "cumbersome" is changing all refs to 'ActiveSheet'
in your code, use Find/Replace (Ctrl+H) and change each sheet module to
refer to itself instead of ActiveSheet (as was rendered by the macro
recorder). That means replace "ActiveSheet" with "Me", and add dots
where needed so your code implements 'fully qualified references'. This
is just 'good programming practice' <IMO> and so is worth
learning/adopting as an inherent programming skill anytime you write
code.

 

[Gord Dibben]

I will give the disable events thing a go, thanks for that.

Let us know how you make out.


Gord

 

[Chris J Denver]

Hi Garry,

thanks for this feedback, it's not mainly the ActiveSheet, it's more
selection. Lots of sorts and changes I've got in the macros run on the
selections, so for those I would need an indirect address. Also I
dynamically give charts data, for which I use the
ActiveChart.SetSourceData functions. Again I guess there's a way to
address charts differently?

Best,

Chris

 

[Chris J Denver]

Gord,

the disable events works great and I also understand how it works
(which is a plus), many thanks for this.

Chris

 

[Gord Dibben]

Thanks for the feedback.


Gord

Gord,

the disable events works great and I also understand how it works
(which is a plus), many thanks for this.

Chris

 

[GS]

Chris J Denver formulated on Tuesday :

thanks for this feedback, it's not mainly the ActiveSheet, it's more
selection. Lots of sorts and changes I've got in the macros run on the
selections, so for those I would need an indirect address. Also I
dynamically give charts data, for which I use the
ActiveChart.SetSourceData functions. Again I guess there's a way to
address charts differently?

Sounds, then, like Gord's suggestion is the better way to go!<IMO>