VBA Editor opens all user Add-In modules by default

[benwin]

Whenever I open the VBA Editor after a fresh start of Excel (e.g. Alt+F11), it opens the modules for any user defined Add-In by default. Is this standard behavior? And is there a way to avoid that?

If that's of interest: These "user defined" Add-Ins are located in my personal folders.

Thanks for any input!
Ben

[Greg M]

Hi Ben,

To avoid the above situation, you need to use the "Workbook_Open" event to install whatever Add-Ins are appropriate to the workbook you're opening, and then use the "Workbook_BeforeClose" event to uninstall those Add-Ins when your workbook is being closed.

E.g.

Please Login or Register  to view this content.

The above code should be entered in the "ThisWorkbook" VBA CodeModule.

Hope this helps - please let me know how you get on with it.

Regards,

Greg M

[benwin]

Thank you Greg, but I probably didn't make my issue sufficiently clear.
I do want these Add-Ins to be loaded by default, i.e. their functionality available in every notebook I open.

The issue I have is only with the annoying fact that the windows of all these modules and even the PERSONAL.XLSB module are open when I open the VBA editor. So I have about 15 or 20 opened windows in the editor, and have to close/minimize one by one by hand to get to the one I want to edit. No matter whether I close them all, fold the project tree to the highest level ... whenever I reopen Excel and then the VBA editor, all module windows will be open again.

If this were unalterable standard behavior there'd probably be more threads like this - so I assume it isn't and there is a way around it. I just can't see it.

[Greg M]

Hi again,

In the VBA Editor, create a Reference (Tools >> References...) to "Microsoft Visual Basic for Applications Extensibility n.n" and then insert the following code into the "ThisWorkbook" VBA CodeModule of a new workbook:

Please Login or Register  to view this content.

The above routine will close all CodeModule and Form windows in the VB Editor.

To fully achieve what you're looking for you will need to save the above workbook in the folder specified in the StartUp or Alternate StartUp File Location, so that it is opened every time Excel starts.

Hope this helps - please let me know how you get on with it.

Regards,

Greg M

[benwin]

Thank you Greg. I followed your suggestion, but ran into two problems.
Referencing "Microsoft Visual Basic for Applications Extensibility n.n" lead to an error/warning that this conflicts with other active references/modules (?) - I cannot reproduce the message. The other active references are

• Visual Basic for Applications
• Microsoft Excel 14.0 Object Library
• OLE Automation
• Microsoft Windows 14.0 Object Library

Should I expect any real conflicts here?

Anyway, with the reference active, I now get (translated)

Error 1004
The programmatic access to the Visual Basic Project is not safe

It points to the code line

Please Login or Register  to view this content.

I have no experience with these things, could you help me out once more?

[Greg M]

Hi again,

I'm not sure why you're getting the "this conflicts with other active references/modules" message - it's not something I've ever encountered.

If the other message you're getting is "The programmatic access to the Visual Basic Project is not trusted" then the way to overcome that is as follows:

In the main Excel window (i.e. not the Visual Basic Editor window),

Click on the "File" tab in the Ribbon
Click on the "Options" icon in the left-hand column
Click on the "Trust Center" option in the left-hand column

Click on the "Trust Center Settings..." button

Click on the "Macro Settings" option in the left-hand column

Tick the "Trust access to the VBA project object model
Click on "OK"

Click on "OK"

Hope this helps - please let me know how you get on with it.

Regards,

Greg M

[Izandol]

Perhaps you may try Tools-Options-General-'Collapse Proj. hides Windows' option. It will allow you to close the windows quickly at least.

[benwin]

@ Greg: Apparently, our admins won't allow me to access that option, it's greyed out.

@Izandol: I have this option selected, and it does work. But while Excel does remember the state of the project (collapsed or not collapsed), it only closes the respective windows after I unfold and recollapse the project. Since I usually have just one module per project, it doesn't make much of a difference to closing the windows individually.

Thanks to both of you nonetheless!
What I didn't understand yet: Is the behavior standard? Do your project modules and PERSONAL.XLSB open by default? I think there was a time when mine didn't, but I'm not certain.

[Izandol]

I think it is the default but, as you, I think this was not always the case.

[benwin]

Thanks!

Regards,
Ben

[Kyle123]

If you password protect the projects of the add ins, I suspect this will override any default behaviour and prevent them from opening.

[Izandol]

It seems that when you close the module windows and then save the workbook, the windows do not reopen automatically thereafter.

[Greg M]

Hi Ben,

Thanks for taking the trouble to post your feedback - I guess your Admins have their own reasons for not making that option available.

Best regards,

Greg M