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

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,111
Office Version
  1. 2016
Platform
  1. Windows
Try this :
Code:
Sub Test()
    MsgBox CreateObject("WScript.Shell").RegRead _
    ("HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\VBAWarnings")
End Sub
 

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,056
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,111
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Jun 28, 2003
Messages
412
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,111
Office Version
  1. 2016
Platform
  1. Windows
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

Code:
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 :
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top