VBA workbook open does not run after enable content prompt

0
votes

I have a situation where I save a macro-enabled workbook to a untrusted folder (like my users will probably do). When I open the file from Excel (most recent list or File > Open), the file opens and I get the warning banner and click Enable Content. I have a "Hello World" msgBox at the top of the Workbook_Open routine, but the message (and hence the rest of the code) does not appear.

If I close and restart Excel, then open the file, the Workbook_Open event runs.

Any ideas how to solve this? Having users set up trusted locations on multiple folders they might be saving to is not a good solution, nor is requiring them to shutdown and restart Excel workable either.

Thanks, folks!

3
vbaexcel
Generally speaking security settings can't be bypassed with VBA. THis is for very good reason and (obviously) by design. You might consider a bypass method to alert the users of what manual steps must be taken: ozgrid.com/forum/showthread.php?t=16599 – David Zemens
I think you're saying that if the file is opened from within an active session of Excel, despite clicking "Enable Content", the content is not enabled - and the Workbook_Open event doesn't fire? But if Excel has nothing else open the event fires? – CLR
A digital signature (of the VBA code) that is trusted by the users might help here – Pᴇʜ
It isn't that you need to close and restart Excel. You have to save the workbook to your disc after enabling content. Next time you open the workbook the banner will not appear and the Open event will fire. – Variatus
David, I'm not trying to bypass security. Only trying to get the Workbook_Open routine to run after the user has closed the file, re-opens it (in the same Excel instance) and clicks "Enable Content". – Gary B

3 Answers

0
votes

Copypasta:

https://excelribbon.tips.net/T012873_Always_Opening_a_Workbook_that_is_Editable.html

If you are actually wanting to make sure that the real "Protected View" is turned off, that is a different story. The settings for Protected View are controlled on a system-by-system basis in the Trust Center. You can see the settings by following these steps:

  1. Display the Developer tab of the ribbon.
  2. Click Macro Security in the Code group.
  3. Excel displays the Trust Center dialog box. At the left side of the dialog box click Protected View. (See Figure 1.)

enter image description here

Figure 1. The Trust Center dialog box.

The actual Protection View settings available depend on the version of Excel you are using. As mentioned, these settings are controlled at a system level; they are not handled on a workbook-by-workbook basis. The upshot is that they cannot be circumvented by macro code. If they could, then it would render a system completely vulnerable to whatever code was in a workbook being opened—we would be back to the days of macro viruses that were common with some earlier versions of Office products.

Additional information on how the Protected View settings operate can be found on this Microsoft web site:

http://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx

Whether a workbook opens in Protected View or not depends on a user's system settings. This means that control of what opens is entirely up to the individual user and cannot be "forced" by a workbook author. There are ways, though, that the impact of this can be mitigated, and they have to do with trust. Note that at the left of the Trust Center dialog box there are three settings that control this relative to workbooks:

  • Trusted Publishers. Workbooks from whatever publishers are listed in this area are trusted and will open directly, bypassing Protected View.
  • Trusted Locations. Workbooks stored in the locations listed in this area are trusted and will open directly, bypassing Protected View.
  • Trusted Documents. Individual workbooks noted in tis area are trusted and will open directly, bypassing Protected View.

For your workbook, then, to bypass Protected View, you need to be a trusted publisher, the workbook needs to be stored in a trusted location, or it needs to be noted on the system as a trusted document. All of these settings are, again, under control of the user and cannot be modified through macro code.

0
votes

From your description of the issue, David and Peh's answers and comments here are all relevant and correct. Signing and altering your trust settings will resolve this. I wanted to add this as a comment, but it's too long.

However, if you're wondering why Excel is inviting you to [Enable Content], but then doesn't enable macros on request (how I read your question) then I'd wonder that too. If there were settings that disallowed the macros to run, it would/should not offer enabling to you.

If you recreate the issue, and click [Enable Content], can you manually run a macro by switching to the VBE editor and clicking F5/F8? I suspect not. I suspect that when you're clicking [Enable Content], Excel is not inviting enabling Macros at all, but instead perhaps Data connections or something similar.

0
votes

I believe I've found a solution to the problem. I added Application.EnableEvents = True before the code saves both versions of the file and also before closing the open file. It seems as if the file is saved with events disabled, the events won't be enabled when re-opened in the same instance of Excel. I would have thought that events are always re-enabled upon open, but didn't seem to be so in my case. Thanks all for your replies and time!