What are the differences between the microsoft excel security notice popup and the message bar in excel 2013?

Question

I am encountering an issue developping a VBA functionality in an excel Workbook. This workbook is geared toward being used by end-users without administration rights, who need to activate the macros on this specific workbook before being able to use it.

To this end, I have done the following:

• I created a "temporary home worksheet" which asks the user to activate the macros (no code, a simple message in the cells of this worksheet), which is initially visible
• I created a "home worksheet" which is initially (very) hidden, and which holds buttons and other controls that can be used
• I wrote a simple sub which is called:
  • when the workbook is opened and the activesheet is the "temporary home worksheet"
  • or when the "temporary home worksheet" is activated
• This sub does the following (see code below):
  • hide the "temporary home worksheet" (to very hidden)
  • show the "home worksheet"

This enables to:

• show a message asking the user to activate the macros if he/she has not done it
• redirect to the target "home worksheet" if the user activate the macros while the "temporary home worksheet" is activated
• redirect to the target "home worksheet" if the macros are activated and the user activates the "temporary home worksheet"

' Code to hide temporary ws and show target ws

Private Sub setTargetVisibility()

    Dim ws As Excel.Worksheet
    
    ThisWorkbook.Activate
    
    Set ws = ThisWorkbook.Sheets(getParm("tempHomeWSName"))
    With ws
        .Visible = xlSheetVeryHidden
    End With
    
    Set ws = ThisWorkbook.Sheets(getParm("homeWSName"))
    With ws
        .Visible = xlSheetVisible
        .Activate
    End With

End Sub

This works fine most of the time, because the users get prompted to activate the macro through the message bar:

However, they sometimes get another prompt to activate the macros: a microsoft excel security notice popup. Whenever they activate the macro through this popup, they get a runtime error 1004 (e.g. the method Activate of the _workbook object failed), in the sub with the code above.

Edit: an important thing to note is that this excel file is opened in Protected view as it is generated by a web application and downloaded by the user before use.

What cause these different way of enabling macro in excel 2013 (message bar vs. popup)? What are the behaviour differences between them?

Thanks in advance.

Regards,

Answer 1

By default, when you first open a macro-enabled workbook you’ll see a yellow “SECURITY WARNING” bar appear just underneath the ribbon. Clicking the “Enable Content” button will enable macros.

• This will trigger any macros that run when the workbook is opened, so don’t click this by mistake!

If the Visual Basic Editor is open at the time you are opening the file with macros, the Microsoft Excel Security Notice will be displayed.

• If you trust the source of the file and know that all the macros are secure, click the Enable Content or Enable Macros button. This will turn on the macros and make the file a trusted document. The next time you open the workbook, the security warning won't appear.
• If the source of the file is unknown and you don't want to enable macros, you can click the 'X' button to close the security warning. The warning will disappear, but macros will remain disabled. Any attempt to run a macro will result in the following message

Answer 2

As mentionned by @David Donayo in the accepted answer, what triggers the Microsoft Excel Security Notice popup vs. the message bar warning is the fact that the VBA Editor is already open for another xlsm file.

What caused my issue (runtime error 1004 when trying to make Object Model calls such as Application.Calculate or ThisWorkbook.Activate at WorkbookOpen event) is that the issue described here (https://support.microsoft.com/en-us/help/2745652/object-model-calls-may-fail-from-workbookopen-event-when-exiting-prote) is still present in excel 2013 when the Protected View is exited and then the macros are activated through the Microsoft Excel Security Notice.

This issue is not raised when the Protected View is exited and the macros are activated via the message bar warning.

I did not implement the workaround described in the link above (defer Object Model calls from the WorkbookOpen event to the WorkbookActivate event if the workbook is opened in Protected view) as in my context end users should not have the VBA editor open. Therefore I cannot tell whether this workaround works, but see no reason why it should not.