Forum Discussion

r12344321's avatar
r12344321
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 corporate domain without granting the “Full Control” permission, and enabled programmatic access to allow VBA automation.

Goals

  • Prevent the workbook from being shared or opened outside the company domain;
  • Keep certain sheets, rows, and columns protected (formulas, formatting, etc.);
  • Allow VBA macros to edit protected cells, insert new rows, and update contents without unprotecting the sheet — or, alternatively, to temporarily unprotect and reprotect via VBA.

Issue

Even with IRM enabled and programmatic access allowed, VBA cannot write to protected cells or insert rows.
If the user has “Full Control” permission under IRM, everything works — but then the user can also disable IRM protection, which defeats the security purpose.

Question

Is there any additional configuration, specific permission, or MIP/DLP policy that allows VBA to perform full programmatic operations within the domain without requiring “Full Control” for users?

Environment

  • Microsoft 365 E3
  • Excel Desktop (64-bit)
  • IRM/MIP enabled via Microsoft Purview
  • Stored on SharePoint Online

Suggested tags (add via the forum UI)

Excel, VBA, IRM, Microsoft Purview, MIP, DLP, SharePoint Online, Information Protection

Macros and VBA
  • NikolinoDE's avatar
    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.

4 Replies

  • r12344321's avatar
    r12344321
    Copper Contributor
    Nov 04, 2025

    Thank you for the clear explanation and confirmation.
    That matches our test results Your clarification helps us align our design with Excel’s current IRM limitations. Thanks again for your time and support.

  • NikolinoDE's avatar
    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

    1. Keep IRM label on master workbook (read-only for users).
    2. Build a macro-enabled “editor” workbook with internal security (password-protected sheets).
    3. 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's avatar
      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's avatar
        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

        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.

Resources