Workbook_Activate question
 

Good afternoon, all!

If, via VBA, I open a workbook that contains a workbook_activate macro, will
it run when the workbook opens (and by default activates), or only if I
reference it call it specifically from within my calling macro?

Thanks in advance

Pete

Workbook_Activate question
 

Pete,

Yes, the Activate event will run, after the Open event runs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Peter Rooney" wrote in
message
...
Good afternoon, all!

If, via VBA, I open a workbook that contains a
workbook_activate macro, will
it run when the workbook opens (and by default activates), or
only if I
reference it call it specifically from within my calling macro?

Thanks in advance

Pete

Workbook_Activate question
 

Chip,

Oh dear. Just for your info, I have a template workbook which is used to
create other workbooks using Save As. I then produce consolidations using VAB
by opening these new workbooks and copying and pasting to the master
workbook. Each workbook has Workbook_Open, Workbook_BeforeClose, Activate and
deactivate macros.

The only problem is, something in my deactivate macro is clearing the
Windows clipboard, which means that when I activate the target worksheet,
there's nothing to paste. I've isolated which sub macro reference in my
deactivate macro causes this to happen, but I don't know if it empties the
clipboard when its called by the deactivate in the consolidating workbook, or
by the deactivate in the workbook that is being consolidated. As I use save
As, each copy of the workbook is saved with an identical structure, macro
sheets etc.

So now at least I know that my problem could be being caused in two places,
as against one!

Just what I need on a Friday afternoon :-)

If you have any thoughts on things that empty the clipboard without you
knowing it, I'd be delighted to know about them!

In the meantime, if you don't reply before 16:30 (about 15 minutes away),
have a good weekend!

Regards

Pete



"Chip Pearson" wrote:

Pete,

Yes, the Activate event will run, after the Open event runs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Peter Rooney" wrote in
message
...
Good afternoon, all!

If, via VBA, I open a workbook that contains a
workbook_activate macro, will
it run when the workbook opens (and by default activates), or
only if I
reference it call it specifically from within my calling macro?

Thanks in advance

Pete

Workbook_Activate question
 

Use
Application.EnableEvents = False
' do your copy and paste
Application.EnableEvents = True

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Chip,

Oh dear. Just for your info, I have a template workbook which is used to
create other workbooks using Save As. I then produce consolidations using
VAB
by opening these new workbooks and copying and pasting to the master
workbook. Each workbook has Workbook_Open, Workbook_BeforeClose, Activate
and
deactivate macros.

The only problem is, something in my deactivate macro is clearing the
Windows clipboard, which means that when I activate the target worksheet,
there's nothing to paste. I've isolated which sub macro reference in my
deactivate macro causes this to happen, but I don't know if it empties the
clipboard when its called by the deactivate in the consolidating workbook,
or
by the deactivate in the workbook that is being consolidated. As I use
save
As, each copy of the workbook is saved with an identical structure, macro
sheets etc.

So now at least I know that my problem could be being caused in two
places,
as against one!

Just what I need on a Friday afternoon :-)

If you have any thoughts on things that empty the clipboard without you
knowing it, I'd be delighted to know about them!

In the meantime, if you don't reply before 16:30 (about 15 minutes away),
have a good weekend!

Regards

Pete



"Chip Pearson" wrote:

Pete,

Yes, the Activate event will run, after the Open event runs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Peter Rooney" wrote in
message
...
Good afternoon, all!

If, via VBA, I open a workbook that contains a
workbook_activate macro, will
it run when the workbook opens (and by default activates), or
only if I
reference it call it specifically from within my calling macro?

Thanks in advance

Pete

Workbook_Activate question
 

Tom!

You're an absolute GENIUS!

This is EXACTLY what I wanted.

By disabling the Workbook_Deactivate event, the ScreenReset macro doesn't
kick in, which doesn't change the display options, which doesn't empty the
Clipboard!

You've solved ALL my problems from all the posts I've left recently.

I'd seen enableevents, but I didn't really understand what it did.

If I could give you ten green ticks, I would! :-)

Sorry to go over the top, but this has helped me out of an enormous hole.

Thanks very much!

Pete





"Tom Ogilvy" wrote:

Use
Application.EnableEvents = False
' do your copy and paste
Application.EnableEvents = True

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Chip,

Oh dear. Just for your info, I have a template workbook which is used to
create other workbooks using Save As. I then produce consolidations using
VAB
by opening these new workbooks and copying and pasting to the master
workbook. Each workbook has Workbook_Open, Workbook_BeforeClose, Activate
and
deactivate macros.

The only problem is, something in my deactivate macro is clearing the
Windows clipboard, which means that when I activate the target worksheet,
there's nothing to paste. I've isolated which sub macro reference in my
deactivate macro causes this to happen, but I don't know if it empties the
clipboard when its called by the deactivate in the consolidating workbook,
or
by the deactivate in the workbook that is being consolidated. As I use
save
As, each copy of the workbook is saved with an identical structure, macro
sheets etc.

So now at least I know that my problem could be being caused in two
places,
as against one!

Just what I need on a Friday afternoon :-)

If you have any thoughts on things that empty the clipboard without you
knowing it, I'd be delighted to know about them!

In the meantime, if you don't reply before 16:30 (about 15 minutes away),
have a good weekend!

Regards

Pete



"Chip Pearson" wrote:

Pete,

Yes, the Activate event will run, after the Open event runs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Peter Rooney" wrote in
message
...
Good afternoon, all!

If, via VBA, I open a workbook that contains a
workbook_activate macro, will
it run when the workbook opens (and by default activates), or
only if I
reference it call it specifically from within my calling macro?

Thanks in advance

Pete