How to know if "disable all macros with notification" is ticked?

[actjfc]

Excel friends,

I found the thread below to know if "Trust Access Visual Basic Project" is ticked. It works perfectly, but in addition to it, I also need to know if "disable all macros with notification" is ticked. The ideal will be to identify which one of the four is ticked.

Is there any way to find it out using VBA?

https://www.mrexcel.com/forum/excel...trust-access-visual-basic-project-ticked.html

I appreciate any help or ideas.

Thanks!

 

[JLGWhiz]

If "Disable all macros with notification" is ticked, you will get an alert message when you open a file containing macros that tells you "Macros have been disabled".

If you want to know the settings, then visit your security center. There probably is a way to find out with VBA, but the settings are permanent in nature and are normally done manually for security reasons.

 

[Jaafar Tribak]

Try this :

Sub Test()
    MsgBox CreateObject("WScript.Shell").RegRead _
    ("HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\VBAWarnings")
End Sub

 

[actjfc]

Thanks for the replies:

In my case, some users have "Disable all macros" set by default. They do not know how to change it, and Excel did not show any warnings about this setting. On the other hand, the registry test option says "unable to open the registry key". Do I need to be an admin to read and write the registry? I cannot get autorization to read or change the resgistry of somebody else. The code: Application.VBE.VBProjects.Count > 0 identify if the Trust VBA si cheked. I thought it will be a similar 'easy' code to identify if "disable all macros with notification" is checked.

My goal is to identify the PC Macro Settings options. If the "disable all macros with notification" is checked if fine for me, but any other setting will show a msgbox asking the user set it up to "disable all macros with notification" and also Trust VBA. Then the code Application.CommandBars.ExecuteMso ("MacroSecurity") will popup the macro settings screen.

Thanks for any help!

 

[RoryA]

If Disable all macros is set, your VBA is not going to run anyway.

 

[Jaafar Tribak]

In my case, some users have "Disable all macros" set by default. They do not know how to change it, and Excel did not show any warnings about this setting.

The only thing I can think of is to have a welcome sheet that asks the user to enable macros and shows them how to do it if they don't know.

Take a look here

 

[actjfc]

There are about 200 users. So, I developed a Workbook_Open macro that checks if Trust VBA Objects is checked. If, so it will show a message and popup the Macro Setting screen using Application.CommandBars.ExecuteMso ("MacroSecurity"). It works perfect, BUT if the user has the Trust VBA Objects already checked, and the "Disable all macros" also checked by default. My pop-up macro does not work, and what it is worst they do not realize it because there is no warning. I assume there is a catch 22. I see what you mean. Any final thoughts?

 

[Jaafar Tribak]

There are about 200 users. So, I developed a Workbook_Open macro that checks if Trust VBA Objects is checked. If, so it will show a message and popup the Macro Setting screen using Application.CommandBars.ExecuteMso ("MacroSecurity"). It works perfect, BUT if the user has the Trust VBA Objects already checked, and the "Disable all macros" also checked by default. My pop-up macro does not work, and what it is worst they do not realize it because there is no warning. I assume there is a catch 22. I see what you mean. Any final thoughts?

The welcome sheet trick that I suggested in the link is the known way to force Enable Macros .. Other than that, there is another tacky workaround via the use of code in an addin (assuming that addins are activated in the excel security\addins settings in the users machines)

1- You add the following code in the addin Thisworkbook module

Option Explicit

Private WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    Dim sFullname As String
    With Application
        If Wb.Name = "[B]ABCD.xls[/B]" Then '[COLOR=#008000][B]<== Replace with actual workbook name.[/B][/COLOR]
            If .CommandBars(1).Controls(1).Tag = vbNullString Then
                .CommandBars.ExecuteMso ("MacroSecurity")
                sFullname = Wb.FullName
                Wb.ChangeFileAccess xlReadOnly
                Wb.Close False
                Workbooks.Open sFullname
            Else
                .CommandBars(1).Controls(1).Tag = vbNullString
            End If
        End If
    End With
End Sub

2- Place this code in the Thisworkbook module of the actual workbook :

Private Sub Workbook_Open()
    Application.CommandBars(1).Controls(1).Tag = "dummy"
End Sub

The above should trick should cause the MacroSecurity diaolg to be displayed whenever the workbook is opened with Macros disabled.

Obviously, I don't think distributing and installing an addin on 200 users machines is practical.