Read-Only Flag Remover: Stop That Annoying 'Open as Read-Only?' Prompt Forever

TL;DR: A client sends you a workbook. Every time you open it, Excel asks “This file is read-only recommended. Open as read-only?” You always click No. After the 15th time, you want the flag gone forever. This macro finds it, tells you what it found, and strips it in one click. Save and you’ll never see the prompt again.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook that prompts “Open as Read-Only?” every time you open it, or shows the yellow “MARKED AS FINAL” banner

What the macro does:

• Checks ThisWorkbook.ReadOnlyRecommended — the flag that triggers the “open as read-only?” prompt
• Checks ThisWorkbook.Final — the flag that triggers the “Marked as Final” warning banner
• Reports which flags it found
• On confirm, strips both flags
• Reminds you to save the workbook to make the change permanent

What the macro does NOT do:

• It does not remove worksheet or workbook structure protection. If the file actually is read-only (file permissions, not a metadata flag), this macro won’t change that — you need Windows file permissions for that
• It does not remove the “Marked as Final” flag from a file that hasn’t been opened yet. If the file has never been opened (or was opened only in read-only mode), ThisWorkbook.Final still returns True and the macro catches it
• It does not save the file for you. The change takes effect only after you save (Ctrl+S). The macro reminds you

Limitations:

• ThisWorkbook.Final resets to False after you click “Edit Anyway.” If you’ve already dismissed the yellow banner in the current session, the macro can’t detect the flag. The workaround: close and reopen the file, run the macro before clicking “Edit Anyway,” or manually toggle it off via File → Info → Protect Workbook → Mark as Final
• Works on ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook (PERSONAL.XLSB) and replace with ActiveWorkbook to run on any open file
• If the workbook structure is protected, ThisWorkbook.ReadOnlyRecommended may be read-only itself. The error handler will report this

#The Macro

Option Explicit

Sub RemoveReadOnlyFlags()
    ' ── Read-Only Flag Remover ─────────────────────────
    ' Strips two persistent metadata flags from the
    ' active workbook:
    '   1. Read-Only Recommended — the "open as
    '      read-only?" prompt
    '   2. Marked as Final — the yellow warning banner
    '
    ' Reports which flags were found, asks once before
    ' removing, and reminds you to save the file after.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim hasReadOnlyFlag As Boolean
    Dim hasFinalFlag As Boolean
    Dim msg As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Detect flags ───────────────────────────────────
    hasReadOnlyFlag = ThisWorkbook.ReadOnlyRecommended

    On Error Resume Next
    hasFinalFlag = ThisWorkbook.Final
    On Error GoTo CleanUp

    ' ── Nothing to remove ──────────────────────────────
    If Not hasReadOnlyFlag And Not hasFinalFlag Then
        MsgBox "No read-only flags found on this workbook.", _
               vbInformation, "Read-Only Flag Remover"
        GoTo CleanUp
    End If

    ' ── Report findings ────────────────────────────────
    msg = "Found the following flags:" & vbCrLf & vbCrLf
    If hasReadOnlyFlag Then
        msg = msg & "  • Read-Only Recommended" & vbCrLf
    End If
    If hasFinalFlag Then
        msg = msg & "  • Marked as Final" & vbCrLf
    End If
    msg = msg & vbCrLf & "Remove these flags? You must save" & _
          " the workbook after to make the change permanent."

    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm Flag Removal") = vbNo Then
        MsgBox "No flags were removed.", vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    ' ── Remove flags ───────────────────────────────────
    If hasReadOnlyFlag Then
        ThisWorkbook.ReadOnlyRecommended = False
    End If

    If hasFinalFlag Then
        On Error Resume Next
        ThisWorkbook.Final = False
        If Err.Number <> 0 Then
            MsgBox "The Marked as Final flag could not be " & _
                   "removed automatically." & vbCrLf & vbCrLf & _
                   "To remove it manually: File → Info → " & _
                   "Protect Workbook → Mark as Final (toggle off).", _
                   vbExclamation, "Partial Success"
        End If
        On Error GoTo CleanUp
    End If

    MsgBox "Flags removed. Save the workbook (Ctrl+S) to " & _
           "make the change permanent.", _
           vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & vbCrLf & _
               "The workbook may be protected. Unprotect it " & _
               "first (Review → Protect Workbook) and try again.", _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two flags, two properties — but they live in different places

Excel has two distinct file-level metadata flags that block or warn you about editing. They’re separate features with separate properties, but they cause the same frustration:

Read-Only Recommended (ThisWorkbook.ReadOnlyRecommended): This is a simple Boolean property stored in the workbook’s document properties. When True, Excel displays a dialog at file-open time: “This file is read-only recommended. Open as read-only?” It’s a suggestion, not a restriction — you can click No and edit normally. The flag survives every Save and persists until someone explicitly sets it to False.

Marked as Final (ThisWorkbook.Final): This is a newer feature (Excel 2010+) that changes the file’s status in a more aggressive way. When True, the file opens in read-only mode with a yellow banner across the top. All editing commands are disabled until you click “Edit Anyway.” Unlike Read-Only Recommended, this flag can reset itself — clicking “Edit Anyway” effectively sets Final = False for the current session.

The macro checks both independently and reports what it found before acting:

hasReadOnlyFlag = ThisWorkbook.ReadOnlyRecommended

On Error Resume Next
hasFinalFlag = ThisWorkbook.Final
On Error GoTo CleanUp

The On Error Resume Next wrapper around ThisWorkbook.Final is deliberate. In Excel 2016 and earlier, the Final property may not exist at all — it’s a newer addition to the object model. Instead of crashing on older Excel versions, the macro quietly moves on and only reports ReadOnlyRecommended if that’s all it found.

#Why the “Marked as Final” check has a fallback

The Marked as Final flag is the trickier of the two. Here’s why:

When you open a file that’s marked as final, Excel opens it in read-only mode with the yellow banner. At this exact moment, ThisWorkbook.Final returns True. The macro can detect and remove it. Perfect.

But if you already clicked “Edit Anyway” before running the macro, Excel set Final = False for you (so you could edit). The macro runs, checks .Final, gets False, and reports “no Marked as Final flag found.” This is technically correct — the flag is gone in the current session — but it might come back next time you open the file if Excel didn’t persist the change.

The workaround is simple: close the file, reopen it, and run the macro before clicking “Edit Anyway.” The macro catches the flag while it’s still active.

If the macro can’t remove the flag via VBA (some Excel configurations block it), it falls back to clear instructions:

If Err.Number <> 0 Then
    MsgBox "The Marked as Final flag could not be " & _
           "removed automatically." & vbCrLf & vbCrLf & _
           "To remove it manually: File → Info → " & _
           "Protect Workbook → Mark as Final (toggle off).", _
           vbExclamation, "Partial Success"
End If

#The confirmation uses vbExclamation — same reason as the hyperlink stripper

Deleting metadata flags is not destructive in the way that deleting hyperlinks or data validation is — your cell values, formulas, and formatting are untouched. But the flags were probably set for a reason (even if that reason was “the client clicked a button they didn’t understand”). The exclamation icon signals “pay attention — you’re changing the file’s protection metadata.”

#No Calculation toggle — this is pure metadata

This macro doesn’t write to any cells. It sets two Boolean properties on the workbook object itself. No formulas recalculate, no sheets redraw, no cells change. Application.ScreenUpdating = False is included as a best practice, but the only visual change is in the message boxes the macro shows you.

#You must save for the change to stick

This is the most important line in the post, and it’s in the final message box:

Flags removed. Save the workbook (Ctrl+S) to make the change permanent.

VBA can set ReadOnlyRecommended = False in memory, but that change only persists when you save the file. Close without saving and the flags come back next time you open it. The macro doesn’t save for you because saving is a destructive action — you should be in control of when it happens. The reminder is your cue.

#Adapt It