Office Admin Explorer

Store macro in Personal Macro Workbook by default

Registry value
fglobalsheet_37_1
Group PolicyStore macro in Personal Macro Workbook by default
German versionMakro standardmäßig in Persönliche Makroarbeitsmappe speichern
Intune settings catalogStore macro in Personal Macro Workbook by default (User)
ScopeUser · HKCU
ADMXexcel16.admx · L_StoremacroinPersonalMacroWorkbookbydefault
Open in the interactive explorer+ Add to my collection

Group Policy path

User Configuration\Policies\Administrative Templates\Microsoft Excel 2016\Excel Options\Security\Trust Center
Benutzerkonfiguration\Richtlinien\Administrative Vorlagen\Microsoft Excel 2016\Excel-Optionen\Sicherheit\Trust Center

Registry

ValueData
HKCU\Software\policies\microsoft\office\16.0\excel\options\binaryoptions
fglobalsheet_37_1Enabled = 1 · Disabled = 0

Not configured = the value does not exist – the application default applies and users may change the setting.

PowerShell

Check the current value (read-only)

# --- Settings ---
$root = 'HKCU:'   # other signed-in user (elevated): 'Registry::HKEY_USERS\<SID>'

$key = "$root\Software\policies\microsoft\office\16.0\excel\options\binaryoptions"
foreach ($name in @('fglobalsheet_37_1')) {
    try   { '{0} = {1}' -f $name, (Get-ItemPropertyValue -Path $key -Name $name -ErrorAction Stop) }
    catch { '{0}: not configured' -f $name }
}

Admin rights needed: HKCU\Software\Policies is read-only for standard users. An elevated session writes to the admin's own HKCU – to set it for another signed-in user, change $root to Registry::HKEY_USERS\<SID>.

Enable

# --- Settings ---
$root = 'HKCU:'   # other signed-in user (elevated): 'Registry::HKEY_USERS\<SID>'

try {
    $key = "$root\Software\policies\microsoft\office\16.0\excel\options\binaryoptions"
    if (-not (Test-Path $key)) { New-Item -Path $key -Force -ErrorAction Stop | Out-Null }
    New-ItemProperty -Path $key -Name 'fglobalsheet_37_1' -PropertyType DWord -Value 1 -Force -ErrorAction Stop | Out-Null   # Enabled
    'Done.'
}
catch { Write-Error "Failed: $($_.Exception.Message)" }
Disable
# --- Settings ---
$root = 'HKCU:'   # other signed-in user (elevated): 'Registry::HKEY_USERS\<SID>'

try {
    $key = "$root\Software\policies\microsoft\office\16.0\excel\options\binaryoptions"
    if (-not (Test-Path $key)) { New-Item -Path $key -Force -ErrorAction Stop | Out-Null }
    New-ItemProperty -Path $key -Name 'fglobalsheet_37_1' -PropertyType DWord -Value 0 -Force -ErrorAction Stop | Out-Null   # Disabled
    'Done.'
}
catch { Write-Error "Failed: $($_.Exception.Message)" }
Reset to Not configured
# --- Settings ---
$root = 'HKCU:'   # other signed-in user (elevated): 'Registry::HKEY_USERS\<SID>'

$key = "$root\Software\policies\microsoft\office\16.0\excel\options\binaryoptions"
Remove-ItemProperty -Path $key -Name 'fglobalsheet_37_1' -ErrorAction SilentlyContinue
'Policy values removed (Not configured).'

Intune (settings catalog)

Store macro in Personal Macro Workbook by default (User)

Settings catalog → Add settings → search for the name above.

Intune Settings Catalog Viewer ↗

Alternative: custom profile (OMA-URI)
OMA-URI
./User/Vendor/MSFT/Policy/Config/excel16v2~Policy~L_MicrosoftOfficeExcel~L_ExcelOptions~L_Security~L_TrustCenter/L_StoremacroinPersonalMacroWorkbookbydefault
Data typeString
Value<enabled/> · <disabled/>

Cloud Policy

Cloud Policy (config.office.com → Customization → Policy Management) offers many user policies of the Microsoft 365 Apps – search for Store macro in Personal Macro Workbook by default. It also applies to unmanaged devices and wins over GPO and Intune for the same setting.

Microsoft explanation

This policy setting controls the default location for storing macros in Excel.

If this policy setting is enabled, Excel stores macros in users' personal macro workbook.

If you disable or do not configure this policy setting, Excel stores macros in the active workbook from which they are created.

Note: In the user interface (UI), the "Store macro in" drop down list box in the Record Macro dialog box (Macros | Record Macro) allows users to choose whether to store the new macro in the current workbook, a new workbook, or their personal macro workbook (Personal.xlsb), a hidden workbook that opens every time Excel starts.

By default, Excel displays the "Store macro in" box with "This Workbook" already selected in the drop-down list. If a user saves a macro in the active workbook and then distributes the workbook to others, the macro is distributed along with the workbook. If you enable this policy setting, Excel displays the "Store macro in" box with "Personal Macro Workbook" already selected. Users can still select one of the other two options in the drop-down menu.

Same category: Microsoft Excel 2016 › Excel Options › Security › Trust Center

All policies in this category · All 173 policies of excel16.admx