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" <PeterRooney@discussions.microsoft.com> wrote in
message
news:F6A0F4DD-7227-4DF1-AFB9-ABCA38265638@microsoft.com...
> 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
>
>

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" <PeterRooney@discussions.microsoft.com> wrote in
> message
> news:F6A0F4DD-7227-4DF1-AFB9-ABCA38265638@microsoft.com...
> > 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
> >
> >
>
>
>

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

--
Regards,
Tom Ogilvy


"Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
news:56B0BA27-00FA-4621-B2F7-793DCFEBEA7E@microsoft.com...
> 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" <PeterRooney@discussions.microsoft.com> wrote in
> > message
> > news:F6A0F4DD-7227-4DF1-AFB9-ABCA38265638@microsoft.com...
> > > 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
> > >
> > >
> >
> >
> >

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" <PeterRooney@discussions.microsoft.com> wrote in message
> news:56B0BA27-00FA-4621-B2F7-793DCFEBEA7E@microsoft.com...
> > 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" <PeterRooney@discussions.microsoft.com> wrote in
> > > message
> > > news:F6A0F4DD-7227-4DF1-AFB9-ABCA38265638@microsoft.com...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >
>
>
>