Forum Discussion

Copper Contributor

Nov 03, 2025

Solved

Excel + IRM: Allow VBA to edit protected cells / insert rows without granting "Full Control"

We have a corporate Excel workbook used for IT service pricing that contains sensitive data. To protect this information, we enabled IRM (Information Rights Management), restricting access to our co...

Macros and VBA

NikolinoDE

Nov 04, 2025

You want a fully stand-alone Excel workbook, protected via IRM/MIP, where VBA can modify protected cells or insert rows, without granting users Full Control (so far i understand).

Excel + IRM / MIP Limitations

There is no native Excel/MIP setting that allows macros to bypass protected cells under IRM without Full Control. Any attempt to do Sheet.Unprotect or InsertRows via VBA will fail unless the user has Full Control permissions.

Why UserInterfaceOnly = True fails

UserInterfaceOnly allows macros to edit protected sheets without unprotecting them, but this does not override IRM restrictions.
IRM protection is applied at a level below the Excel object model — macros cannot bypass it unless the file is opened by a user with Full Control.

There is no Excel-native way to allow macros to bypass IRM/MIP cell protections without Full Control. IRM is designed intentionally to enforce governance, including programmatic access restrictions.

UserInterfaceOnly = True only works with normal Excel protection, not IRM-protected workbooks.

For fully stand-alone usage, you either:

Avoid IRM for the editable areas and rely on sheet protection + passwords, or
Use a split model where the IRM master file is read-only, and macros write to a separate unprotected workbook.

If your goal is stand-alone VBA automation with cell editing under IRM, Microsoft’s current model does not support it without granting Full Control. Any workaround requires either:

Relaxing IRM to allow normal sheet protection, or

External service identity (Power Automate, service account) to handle edits under IRM.

Excel-native alternatives (stand-alone)

Given that you cannot bypass IRM programmatically without Full Control, the closest Excel-native options are:

Approach	Pros	Cons
Use normal sheet protection (password) instead of IRM for cells/macros	UserInterfaceOnly=True works; macros can insert rows/update formulas	Does not prevent copying file outside the domain
Split workbook into master (IRM) + editable local sheets	Sensitive data remains IRM-protected; users edit local sheet macros	Adds complexity, requires sync logic
VBA temporarily unprotect with a password	Stand-alone macros work on protected cells	Password must be embedded (security risk)

I apologize that I cannot offer a reasonable solution, but the available options do not allow it.

My answers are voluntary and without guarantee!

Hope this will help you.

NikolinoDE

Platinum Contributor

Nov 04, 2025

Goal	Supported by IRM?
Prevent external sharing	Fully supported via IRM/MIP
Lock structure but allow VBA edits	Not possible without Full Control
Allow VBA edits under service context	Possible with automation identity
Sheet protection with macro access	Use Excel protection, not IRM

Recommended Enterprise Model

Keep IRM label on master workbook (read-only for users).
Build a macro-enabled “editor” workbook with internal security (password-protected sheets).
Use the macro to write updates through:

SharePoint REST API, or
Excel Online Connector (Power Automate) using a service account with Full Control.

That preserves your data sovereignty and corporate controls without granting users Full Control rights.

Simple VBA that POSTs to Power Automate HTTP endpoint

This is a lightweight client example. It does not impersonate elevated rights — it just sends data.

Sub SendToPA()
Dim http As Object
Dim url As String
Dim sJSON As String
Dim dateVal As String

url = "https://prod-XX.westus.logic.azure...." ' Power Automate HTTP trigger URL (use managed auth in prod)
dateVal = Format(Range("A2").Value, "MM/DD/YYYY")

sJSON = "{" & Chr(34) & "user" & Chr(34) & ":" & Chr(34) & Environ("USERNAME") & Chr(34) & "," & _
Chr(34) & "date" & Chr(34) & ":" & Chr(34) & dateVal & Chr(34) & "}"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "POST", url, False
http.setRequestHeader "Content-Type", "application/json"
' If using a static shared key (not recommended), include header
' http.setRequestHeader "x-api-key", "<secret-from-keyvault>"
http.send sJSON

If http.Status = 200 Then
MsgBox "Update submitted successfully."
Else
MsgBox "Error: " & http.Status & " - " & http.responseText
End If
End Sub

Do NOT embed long-lived secrets in client-side VBA. Use short lived tokens or require the VBA call to include user identity so Power Automate can validate and prompt for approval.

This is a suggestion.

My answers are voluntary and without guarantee!

Hope this will help you.

r12344321

Copper Contributor

Nov 04, 2025

Thank you very much for the detailed answer and guidance.

The architecture you described with Power Automate and a service identity makes perfect sense from a governance and compliance standpoint.

However, in our current scenario, we’d prefer to keep the workbook fully stand-alone — without external connectors or Power Automate flows — since it’s distributed internally to multiple business units that work offline or in restricted environments

Our goal is to understand whether there’s any Excel-native configuration (IRM, MIP label, or registry policy) that would allow VBA macros to edit protected cells or use UserInterfaceOnly = True under IRM, without granting “Full Control” to users.

NikolinoDE

Platinum Contributor

Nov 04, 2025

You want a fully stand-alone Excel workbook, protected via IRM/MIP, where VBA can modify protected cells or insert rows, without granting users Full Control (so far i understand).

Excel + IRM / MIP Limitations

Why UserInterfaceOnly = True fails

UserInterfaceOnly allows macros to edit protected sheets without unprotecting them, but this does not override IRM restrictions.
IRM protection is applied at a level below the Excel object model — macros cannot bypass it unless the file is opened by a user with Full Control.

UserInterfaceOnly = True only works with normal Excel protection, not IRM-protected workbooks.

For fully stand-alone usage, you either:

Avoid IRM for the editable areas and rely on sheet protection + passwords, or
Use a split model where the IRM master file is read-only, and macros write to a separate unprotected workbook.

If your goal is stand-alone VBA automation with cell editing under IRM, Microsoft’s current model does not support it without granting Full Control. Any workaround requires either:

Relaxing IRM to allow normal sheet protection, or

External service identity (Power Automate, service account) to handle edits under IRM.

Excel-native alternatives (stand-alone)

Given that you cannot bypass IRM programmatically without Full Control, the closest Excel-native options are:

Approach	Pros	Cons
Use normal sheet protection (password) instead of IRM for cells/macros	UserInterfaceOnly=True works; macros can insert rows/update formulas	Does not prevent copying file outside the domain
Split workbook into master (IRM) + editable local sheets	Sensitive data remains IRM-protected; users edit local sheet macros	Adds complexity, requires sync logic
VBA temporarily unprotect with a password	Stand-alone macros work on protected cells	Password must be embedded (security risk)

I apologize that I cannot offer a reasonable solution, but the available options do not allow it.

My answers are voluntary and without guarantee!

Hope this will help you.