?
Solved

Excel workbook referencing error: VBA VB

Posted on 2010-08-18
10
Medium Priority
?
624 Views
Last Modified: 2012-05-10
Hi,

I have two macros in excel vba.  One reference the other to crate dynamic menus (i.e.: the macro that creates the menu has permission for access to VBA Project).  We intermittently get the message "This workbook is currently referenced by another workbook and cannot be closed when excel is started.  Once it occurs, it stays.

I have included "On error resume next" and "applications.displayalerts = false" code in auto_open in both macros.  All seems fine until windows performs an update.  I do not know if the update has anything to do with this , but it seems to possibly have some correlation.

I can resolve the issue by re-establishing the reference between the macros.  However the macros are deployed as packages to customers in large enterprises and therefore that manual solution is not feasible..

Any idea as to how to prevent this message from showing -- permantly?

Thanks
0
Comment
Question by:damoruso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 33472605
Seems you have two workbooks, one referencing the other and the "other" workbook is closed first
(which -because it is referenced by another workbook open in Excel- causes the warning message to occur).

You could work around it, but the method depends on your code: why the reference?
0
 

Author Comment

by:damoruso
ID: 33475338
This occurs when Excek opens (even if no macros run).

The reason why they are separate is that main macro is password protected and the dynamic menu generator macro doesn't run if it protected.  So we extracted the snippet of code that generates the menu and put it into a separate unprotected macro.  The main macro refers to the menu generator. BTW: the dynamic menu code generates VBA on the fly and accesses VBA Project.

If there is a way that the menu code can work from within the password protected main macro, that would be ideal.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 33483543
I would use a table driven approach to generate the menu. Search the web for "table driven commandbar excel" and you should find examples. I think John Walkenbach has an example somewhere on his site.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:damoruso
ID: 33484619
Sorry. I mis-wrote.  These are actually forms that are being generated, not menus.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 33485364
You don't have to actually generate a form in the VBA project, you can also populate an existing userform object with controls at runtime. That way, you also do not need to allow access to the VBProject. It does require you to use a class module to catch the events of the controls which were added at runtime however.
0
 

Author Comment

by:damoruso
ID: 33485679
That would be great.  So I will be able to dynamically add rows of textboxes and checkbox controls based on the amount of data I have?
0
 
LVL 11

Accepted Solution

by:
jkpieterse earned 2000 total points
ID: 33491258
Exactly.
I have an example project that does something like this. It includes a database file which goes in the same folder as the excel file.
TableDrivenForm002.zip
0
 

Author Comment

by:damoruso
ID: 33497810
This looks good. I'm going through it now.  It seems as though udDataEntry is of most interest for me.  I'll keep you posted.  Thanks!
0
 

Author Closing Comment

by:damoruso
ID: 33664155
Great.  Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question