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

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




On Tue, 29 Nov 2011 06:32:33 -0800 (PST), Chris J Denver
wrote:

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

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


On Nov 29, 4:30*pm, Gord Dibben wrote:
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

On Tue, 29 Nov 2011 06:32:33 -0800 (PST), Chris J Denver

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

On Nov 29, 4:59*pm, GS wrote:
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.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

Let us know how you make out.


Gord

On Tue, 29 Nov 2011 08:43:08 -0800 (PST), Chris J Denver
wrote:

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


On Nov 29, 4:30*pm, Gord Dibben wrote:
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

On Tue, 29 Nov 2011 06:32:33 -0800 (PST), 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

On Nov 29, 5:06*pm, Gord Dibben wrote:
I will give the disable events thing a go, thanks for that.

Let us know how you make out.

Gord

On Tue, 29 Nov 2011 08:43:08 -0800 (PST), Chris J Denver







wrote:
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

On Nov 29, 4:30 pm, Gord Dibben wrote:
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

On Tue, 29 Nov 2011 06:32:33 -0800 (PST), Chris J Denver

Thanks for the feedback.


Gord

On Tue, 29 Nov 2011 09:25:56 -0800 (PST), Chris J Denver
wrote:

Gord,

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

Chris

On Nov 29, 5:06*pm, Gord Dibben wrote:
I will give the disable events thing a go, thanks for that.

Let us know how you make out.

Gord

On Tue, 29 Nov 2011 08:43:08 -0800 (PST), Chris J Denver







wrote:
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

On Nov 29, 4:30 pm, Gord Dibben wrote:
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

On Tue, 29 Nov 2011 06:32:33 -0800 (PST), Chris J Denver