ON_OPEN Question

[David Schrader]

Hello Excel programming gurus (and others),

Assume I have a workbook, call it WB1, open. Then assume that
I open a second workbook (WB2).

Workbook WB2 has nothing in it but data. (It has nothing which
in the way of macros or code of any kind.) Workbook WB1 however
has a large number of macros which will be used to organize,
scan, and reformat the data contained in Workbook WB2.

My question is, is there a way, using macros and code which
is contained in WB1, to capture when WB2 is opened and perform
some set of tasks? I haven't been able to successfully get
the "On_Open" macro in WB1 to fire when I open WB2. (FWIW,
the WB2 names won't always be the same so I can't hardcode
in a specific name to look for.)

Suggestions of how I might be able to do this?

Many thanks, in advance, for any ideas or suggestions.

David

 

[Bob Phillips]

The way to so it is to set a variable to the newly opened workbook, and get
your macros to work on the variable.

Set oWB = Workbooks.Open(filename)

With oWB
do something
.Save
.Close
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Cone]

You could add a button to a toolbar that would call the code in WB1.
That could be done in the workbook_open event in WB1.
(the button should be removed when the workbook closes)
That assumes your code is written to work on the ActiveWorkbook.
You would want your code to display a message box asking if
the intent is to rearrange the workbook
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"David Schrader" <[email protected]>
wrote in message
Hello Excel programming gurus (and others),

Assume I have a workbook, call it WB1, open. Then assume that
I open a second workbook (WB2).

Workbook WB2 has nothing in it but data. (It has nothing which
in the way of macros or code of any kind.) Workbook WB1 however
has a large number of macros which will be used to organize,
scan, and reformat the data contained in Workbook WB2.

My question is, is there a way, using macros and code which
is contained in WB1, to capture when WB2 is opened and perform
some set of tasks? I haven't been able to successfully get
the "On_Open" macro in WB1 to fire when I open WB2. (FWIW,
the WB2 names won't always be the same so I can't hardcode
in a specific name to look for.)

Suggestions of how I might be able to do this?

Many thanks, in advance, for any ideas or suggestions.

David

 

[David Schrader]

Bob,

I hadn't wanted to create, then set, anything in WB2 - I'd
like to leave the data as pristine as possible beyond the
sorting and such.

If nothing else shows up I'll definitely give this a try. (It
is really quite simple and also "almost" elegant.)

David

 

[David Schrader]

Jim,

I already have created a menu which resides in WB1.
This menu already has (a number of) buttons on it all
of which execute one or more macros.

Yes, all of these macros work on the "active" work-
sheet/workbook.

I hadn't considered using a message box to query
whether I wanted to re-arrange the workbook but
I suppose I could if you have some kind of suggestion
based upon that approach. (One thing I failed to say
was I'm trying to keep this as simple as possible be-
cause I want to - if possible - anticipate user stupidity
[meaning their failure{s} to read instructions, etc.]. If
you've done and programming of anything which is to
be used by others you're probably aware that users
can make the more creative interpretations of any
directions/instructions you may provide and respond
with completely unexpected responses.)

Did you have additional suggestions *if* I was
planning to use a button?

David