Divorcing 'referenced' workbooks
 

I have been working with 3 or 4 workbooks open in the same window.
Let's call them A, B, C and D.
I have no formulas that point from one workbook to another.

When I try to close workbook A, I get a message saying cannot close this
workbook, it is referenced by another. A is the only workbook for which I get
the message when I try to close one of them, but not all of them.

What do I look for so I can close them independantly?

Thanks.

--
Neal Z

Divorcing 'referenced' workbooks
 

Hit alt-f11 to get to the VBE.
hit ctrl-r to see the project explorer window.

Notice the names of the workbooks and projects that are listed (like windows
explorer).

VBAProject (Book1.xls)
MyProject (this is my workbook.xls)
and so forth.

Select one of them at a time.
Tools|references.
Look at the list of checked items.

You should see one that is uses that VBAProject (or MyProject).

You can uncheck it to remove the reference. But be careful. It may have been
there on purpose.

The developer may have relied on that reference so he/she could use macros in
that other workbook.

I'd check with the developers first!

Neal Zimm wrote:

I have been working with 3 or 4 workbooks open in the same window.
Let's call them A, B, C and D.
I have no formulas that point from one workbook to another.

When I try to close workbook A, I get a message saying cannot close this
workbook, it is referenced by another. A is the only workbook for which I get
the message when I try to close one of them, but not all of them.

What do I look for so I can close them independantly?

Thanks.

--
Neal Z

--

Dave Peterson

Divorcing 'referenced' workbooks
 

Hi Dave -
Thanks for the prompt answer. What's embarassing is that the developer
you speak about is ME, altho' a beginning one. Lots of years in systems
applications development, (mostly mainframe) but am self learning VBA.
The devil of it is, I removed the offending reference, but am at a
complete loss as to how it got there, since I don't remember 'doing it'
specifically.
I'm developing an app, purely on spec, for a large newspaper where the
route books for the delivery staff are stored in Excel. The macros I'm
working on are in, say, workbook B.
My envisioned method of 'installation', (I'm working with a potential user
community that has only vary BASIC excel skills) is to have the user open up
his/her workbook, A, and then open B in the same window.
The macro's in B will operate on the worksheets in A.
This has worked just fine in testing.
Could this be the culprit in updating that reference field?
Thanks so much,
Neal


"Dave Peterson" wrote:

Hit alt-f11 to get to the VBE.
hit ctrl-r to see the project explorer window.

Notice the names of the workbooks and projects that are listed (like windows
explorer).

VBAProject (Book1.xls)
MyProject (this is my workbook.xls)
and so forth.

Select one of them at a time.
Tools|references.
Look at the list of checked items.

You should see one that is uses that VBAProject (or MyProject).

You can uncheck it to remove the reference. But be careful. It may have been
there on purpose.

The developer may have relied on that reference so he/she could use macros in
that other workbook.

I'd check with the developers first!

Neal Zimm wrote:

I have been working with 3 or 4 workbooks open in the same window.
Let's call them A, B, C and D.
I have no formulas that point from one workbook to another.

When I try to close workbook A, I get a message saying cannot close this
workbook, it is referenced by another. A is the only workbook for which I get
the message when I try to close one of them, but not all of them.

What do I look for so I can close them independantly?

Thanks.

--
Neal Z

--

Dave Peterson

Divorcing 'referenced' workbooks
 

That situation (opening workbook A will open workbook B) seems like a very nice
use for tools|references. (Maybe you did it when you were experimenting???)

When I want a set of macros available to the user--and I don't want to put them
in the same workbook as the data (nice if the macros could change or be used on
lots of different workbooks), I create a separate addin (.xla) that builds a
toolbar that gives the user access to the macros.

If I want to add options to the menubar, I use a variation of John Walkenbach's
menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm

If I want to add a toolbar of my own, here's how I do it:
http://groups.google.co.uk/groups?th...5B41%40msn.com

Then I tell the user to open the addin when they need to run the macro. (I
would let the user load it manually.)

Neal Zimm wrote:

Hi Dave -
Thanks for the prompt answer. What's embarassing is that the developer
you speak about is ME, altho' a beginning one. Lots of years in systems
applications development, (mostly mainframe) but am self learning VBA.
The devil of it is, I removed the offending reference, but am at a
complete loss as to how it got there, since I don't remember 'doing it'
specifically.
I'm developing an app, purely on spec, for a large newspaper where the
route books for the delivery staff are stored in Excel. The macros I'm
working on are in, say, workbook B.
My envisioned method of 'installation', (I'm working with a potential user
community that has only vary BASIC excel skills) is to have the user open up
his/her workbook, A, and then open B in the same window.
The macro's in B will operate on the worksheets in A.
This has worked just fine in testing.
Could this be the culprit in updating that reference field?
Thanks so much,
Neal

"Dave Peterson" wrote:

Hit alt-f11 to get to the VBE.
hit ctrl-r to see the project explorer window.

Notice the names of the workbooks and projects that are listed (like windows
explorer).

VBAProject (Book1.xls)
MyProject (this is my workbook.xls)
and so forth.

Select one of them at a time.
Tools|references.
Look at the list of checked items.

You should see one that is uses that VBAProject (or MyProject).

You can uncheck it to remove the reference. But be careful. It may have been
there on purpose.

The developer may have relied on that reference so he/she could use macros in
that other workbook.

I'd check with the developers first!

Neal Zimm wrote:

I have been working with 3 or 4 workbooks open in the same window.
Let's call them A, B, C and D.
I have no formulas that point from one workbook to another.

When I try to close workbook A, I get a message saying cannot close this
workbook, it is referenced by another. A is the only workbook for which I get
the message when I try to close one of them, but not all of them.

What do I look for so I can close them independantly?

Thanks.

--
Neal Z

--

Dave Peterson


--

Dave Peterson