Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell referenced elsewhere | Excel Discussion (Misc queries) | |||
Keeping data once referenced workbooks are closed. | Excel Discussion (Misc queries) | |||
is it always necessary to open workbooks referenced in a formula | Excel Discussion (Misc queries) | |||
Workbooks referenced by a deleted workbook | Excel Programming | |||
Referenced Workbooks | Excel Programming |