application.activesheet, should I use it?

[oldyork90]

I have a form button placed on a sheet. It is assigned to a fully
qualified macro name, as in wb.xlsm!sheet.addRows.

Concerning the Application.ActiveSheet method.

Can I be sure that an early call to Application.ActiveSheet in the
macro returns the sheet upon which the button resides?

Seem to be true, but I've read, somewhere, that the active sheet may
change at any time and this really isn't to be depended on.

How can I insure that the sheet I retrieve is the sheet where
the button resides?

#2 AND, if it can not be relied on, should you ever use it?

 

[GS]

I have a form button placed on a sheet. It is assigned to a fully

qualified macro name, as in wb.xlsm!sheet.addRows.

Concerning the Application.ActiveSheet method.

Can I be sure that an early call to Application.ActiveSheet in the
macro returns the sheet upon which the button resides?

Seem to be true, but I've read, somewhere, that the active sheet may
change at any time and this really isn't to be depended on.

How can I insure that the sheet I retrieve is the sheet where
the button resides?

#2 AND, if it can not be relied on, should you ever use it?

You can set a fully qualified ref to the sheet executing the macro, at
the top of the macro...

Dim wksCaller As Worksheet
Set wksCaller = ActiveSheet

...so if the macro activates any other sheets during runtime, your code
will always know which sheet the button clicked is on. (Note that
rarely is it necessary to change sheets!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[oldyork90]

You can set a fully qualified ref to the sheet executing the macro, at

the top of the macro...



Dim wksCaller As Worksheet

Set wksCaller = ActiveSheet



..so if the macro activates any other sheets during runtime, your code

will always know which sheet the button clicked is on. (Note that

rarely is it necessary to change sheets!)

Thanks!

That's presently what I do and so far no problems. But is that really "atomic" so to speak. Can I be sure that while the current routine is queuing up the sheet remains active? I ran into a property call parent and am starting to think about that.

 

[GS]

You can set a fully qualified ref to the sheet executing the macro,

Thanks!

That's presently what I do and so far no problems. But is that
really "atomic" so to speak. Can I be sure that while the current
routine is queuing up the sheet remains active? I ran into a
property call parent and am starting to think about that.

Should be no problem so long as the code refs the sheet. There should
be no reason for the sheet to *not* become the active sheet unless your
code deliberately causes that to happen.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion