Excel workbook referencing error: VBA VB

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
damorusoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
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
damorusoAuthor Commented:
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
jkpieterseCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

damorusoAuthor Commented:
Sorry. I mis-wrote.  These are actually forms that are being generated, not menus.
0
jkpieterseCommented:
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
damorusoAuthor Commented:
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
jkpieterseCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damorusoAuthor Commented:
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
damorusoAuthor Commented:
Great.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.