Bulk Sensitivity Labeler: Stamp 'CONFIDENTIAL' on Every Sheet Header at Once

TL;DR: Before emailing or printing a workpackage, your firm’s policy requires a classification label — “CONFIDENTIAL — FOR INTERNAL USE ONLY” — on every page. Excel’s only native way to do this is Page Layout → Print Titles → Header/Footer → Custom Header on every single sheet. This macro asks for the label text and where you want it, then stamps it onto every visible sheet at once.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with any number of sheets — the macro loops every visible one
• The classification text your firm requires (if you don’t know it, ask your office manager or check your firm’s document retention policy)

What the macro does:

• Prompts for the classification label text
• Prompts for where to place it: Center Header (CH), Left Header (LH), Right Header (RH), Center Footer (CF), Left Footer (LF), or Right Footer (RF)
• Sets ws.PageSetup.CenterHeader (or equivalent) on every visible sheet
• Reports how many sheets were labeled

What the macro does NOT do:

• It does not modify cell values, formulas, formatting, or data of any kind
• It does not change existing headers on sheets that already have a custom header — the macro overwrites the specified position. If Sheet 3 already has a right-header label, running this macro with “RH” will replace it
• It does not add the label as a visible watermark on the sheet itself (only print/print-preview headers and footers)

Limitations:

• Hidden sheets (xlSheetHidden and xlSheetVeryHidden) are skipped. Unhide them first with the Unhide All Sheets macro if they need labels
• The label only appears in Print Preview and printed output — it is not visible in the normal worksheet view. To see it, press Ctrl+F2 or go to File → Print after running the macro
• Each position (left, center, right) at each location (header, footer) can only hold one label. Running the macro again at the same position replaces the previous label
• The macro stamps plain text only. For formatted text (bold, font size, color), see the Adapt It section
• If your firm uses different labels for different clients, run the macro once per workbook — the label is Workbook-specific, not computer-specific

#The Macro

Option Explicit

Sub BulkSensitivityLabel()
    ' ── Bulk Sensitivity Labeler ──────────────────────
    ' Stamps a classification label onto the header or
    ' footer of every visible sheet. One InputBox for
    ' the text, one for the placement code.
    '
    ' Placement codes:
    '   CH = Center Header   CF = Center Footer
    '   LH = Left Header     LF = Left Footer
    '   RH = Right Header    RF = Right Footer
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim label As String
    Dim placement As String
    Dim count As Long

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

    ' ── Get classification label ───────────────────────
    label = InputBox("Enter the classification label:" & _
        vbCrLf & "(e.g., 'CONFIDENTIAL — FOR INTERNAL USE ONLY')", _
        "Bulk Sensitivity Labeler")

    If label = "" Then GoTo CleanUp

    ' ── Get placement ──────────────────────────────────
    placement = InputBox("Placement code:" & vbCrLf & vbCrLf & _
        "  CH = Center Header      CF = Center Footer" & vbCrLf & _
        "  LH = Left Header        LF = Left Footer" & vbCrLf & _
        "  RH = Right Header       RF = Right Footer", _
        "Bulk Sensitivity Labeler", "CH")

    If placement = "" Then GoTo CleanUp

    Select Case UCase(placement)
        Case "CH", "LH", "RH", "CF", "LF", "RF"
            ' Valid — continue
        Case Else
            MsgBox """" & placement & """ is not a valid code." & _
                vbCrLf & "Use: CH, LH, RH, CF, LF, or RF.", _
                vbExclamation, "Invalid Placement"
            GoTo CleanUp
    End Select

    ' ── Apply to every visible sheet ───────────────────
    count = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            Select Case UCase(placement)
                Case "CH": ws.PageSetup.CenterHeader = label
                Case "LH": ws.PageSetup.LeftHeader = label
                Case "RH": ws.PageSetup.RightHeader = label
                Case "CF": ws.PageSetup.CenterFooter = label
                Case "LF": ws.PageSetup.LeftFooter = label
                Case "RF": ws.PageSetup.RightFooter = label
            End Select
            count = count + 1
        End If
    Next ws

    ' ── Report results ─────────────────────────────────
    MsgBox "Classification label applied to " & count & _
        " visible sheet(s)." & vbCrLf & vbCrLf & _
        "Preview: Press Ctrl+F2 or File → Print to verify.", _
        vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Headers and footers are page setup metadata

A sheet’s header and footer are stored in the sheet’s page setup properties — they’re not cells, not shapes, not data. Excel exposes them through ws.PageSetup.CenterHeader, ws.PageSetup.RightFooter, and four other placements. Each is a plain string that Excel renders at the top or bottom of every printed page on that sheet.

ws.PageSetup.CenterHeader = label

Because these are metadata properties, not cell values, the macro doesn’t touch any data on any sheet. You can run it right before printing and your numbers, formulas, and formatting are completely undisturbed. The only thing that changes is what appears at the top or bottom of each printed page.

#Why the placement codes instead of radio buttons

VBA doesn’t have a built-in radio-button dialog. The options are:

1. InputBox with codes — simple, works on every Excel install, no external dependencies. The user types two letters.
2. UserForm with radio buttons — looks better but adds 40+ lines of form designer code and requires the user to enable Microsoft Forms 2.0 references
3. Six separate macros — one per placement, no typing needed. But that’s six macros in the macro list instead of one

This macro uses option 1 because it keeps the code under 60 lines and works on every Excel install with zero setup. The placement codes are self-documenting: CH = Center Header, LF = Left Footer, etc. The second InputBox shows a menu of all six options so the user doesn’t need to memorize them.

#Input validation before touching sheets

The macro validates both inputs before modifying any sheet:

1. Empty label — the user clicked Cancel. Exit cleanly (Cancel is an intentional choice, not an error).
2. Empty placement — the user clicked Cancel on the placement dialog. Exit cleanly.
3. Invalid placement — the user typed “top” or “B2” instead of a valid code. Show a message explaining the valid options and exit before touching any sheet.

If label = "" Then GoTo CleanUp
If placement = "" Then GoTo CleanUp
Select Case UCase(placement)
    Case "CH", "LH", "RH", "CF", "LF", "RF"
    Case Else
        MsgBox ... : GoTo CleanUp
End Select

This guard-early approach means the macro never writes to a sheet with invalid input. If you type an invalid code, the MsgBox tells you and exits immediately.

#Why skip hidden sheets

Hidden sheets have page setup properties like any other sheet, but by convention they’re usually template sheets, reference data, or archived versions that won’t be printed. Adding a label to them is unnecessary and potentially confusing if someone later unhides the sheet and sees a label they didn’t expect.

If ws.Visible = xlSheetVisible Then
    ' ... apply label ...
End If

If you need labels on hidden sheets, unhide them with the Unhide All Sheets macro first, run this macro, then re-hide them.

#The message box tells you where to verify

Unlike a macro that changes cell values, you can’t see header/footer changes in the normal worksheet view. The message box explicitly tells you how to check:

MsgBox "Classification label applied to " & count & _
    " visible sheet(s)." & vbCrLf & vbCrLf & _
    "Preview: Press Ctrl+F2 or File → Print to verify.", _
    vbInformation, "Done"

This prevents the “I ran the macro and nothing happened” reaction. The label IS there — you just need to switch to Print Preview to see it.

#Adapt It