Native VBA method to change Excel Macro Setting

Question

supposing this option is already enabled on Excel Trust Center:

Enable all macros (not recommended)

I want to change that setting to:

Disable all except digitally signed macros

I can use the below registry setting and apply by VBA.CreateObject("WScript.Shell")

[HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Security]
"VBAWarnings"=dword:00000003

But, I ask about a native VBA method other than Windows registry editing.
Thanks for your help.

@FaneDuru ,is that means all the settings found in `Trust Center` can not modified by native vba codes? — Leedo, Mar 28 '22 at 11:24
Exactly! But they can be modified using Registry keys. I can show a method to add a folder to `Trusted Locations...` — FaneDuru, Mar 28 '22 at 11:34
@FaneDure ,but the problem that this folder is found on outlook, I already post a question (but no answer yet). concerning current question, I will add answer today that (no native method found) to save time for anyone asking the same question. — Leedo, Mar 28 '22 at 12:38
Allowing the code change permissions of the code would be nonsense, don't you think ? — iDevlop, Mar 29 '22 at 07:42
@iDevlop ,below answer will be used from outlook and not from Excel itself, kindly see this question https://stackoverflow.com/questions/71633696/change-excel-macro-settings-temporarily-from-outlook-when-opening-an-email-on — Leedo, Mar 29 '22 at 07:45

score 0 · Accepted Answer · answered Mar 29 '22 at 07:38

unfortunately, there is no a native VBA method to change Macro Settings.
the available way is by using windows registry key and VBA FileSystemObject (FSO),
Note: you have to add reference to the Microsoft Scripting Runtime Library.

Sub change_Macro_Settings()

  Dim myWS As Object: Set myWS = VBA.CreateObject("WScript.Shell")
   Dim name As String, value As String, stype As String
    name = "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Security\VBAWarnings"
     value = "3"
      stype = "REG_DWORD"
       myWS.RegWrite name, value, stype
  
End Sub

Native VBA method to change Excel Macro Setting

1 Answers1