Gridline Toggle: Show or Hide Every Gridline on Every Sheet in One Click

TL;DR: Your workpaper binder has 28 sheets. Half show gridlines, half don’t, and there’s no way to tell which is which until you click each tab. This macro detects the current state, offers to toggle every visible sheet, and does it in one pass. Run it again to reverse.

The Problem

You’re preparing the Henderson Manufacturing binder for partner review. You’ve spent 45 minutes getting the schedules footed and cross-referenced. Now you need to print the whole thing — 28 tabs, from Cover through JE Log.

You tab through the sheets one at a time and notice: the TB has gridlines, the depreciation schedule doesn’t, the state apportionment sheet has them but they’re light gray and barely visible, and the fixed asset schedule looks like a blank white void. Three different preparers touched this file across three review cycles, and each had their own preference.

To fix it, you go View → Show → Gridlines on every sheet. Twenty-eight times. Then the partner wants a second review copy in two days — but this time she wants gridlines on so she can see cell boundaries while marking up. You do it again. Twenty-eight more clicks.

This macro toggles gridlines on every visible sheet by asking one question. That’s it. One click to hide for a clean presentation. Another click to show for data review. No View tab, no per-sheet hunting, no remembering which sheets you already changed.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with multiple sheets — any inherited binder where gridline display is inconsistent across tabs

What the macro does:

Reads ActiveWindow.DisplayGridlines on the sheet you’re currently viewing to detect the current state (visible or hidden)
Shows a MsgBox: “Gridlines are currently [visible/hidden]. Yes = [Hide/Show] on all visible sheets / No = Cancel”
Activates each visible sheet and sets ActiveWindow.DisplayGridlines to the opposite state
Restores your original active sheet so you land where you started
Reports how many sheets were changed
Hides nothing but gridlines — no data, no formatting, no print settings are modified

Limitations:

Only toggles visible sheets — hidden sheets are skipped. If you later unhide a sheet, it won’t have been toggled
The macro activates each sheet to change its gridline setting, which means Application.ScreenUpdating = False is required to avoid screen flicker. The screen will briefly freeze while the macro runs
Gridline visibility is a per-window setting saved in the workbook. If the file is opened on a different machine or by a different user, the gridline state reflects the last person who toggled them
Does not affect print gridlines — those are set separately via Page Setup → Sheet → Print → Gridlines

#The Macro

Option Explicit

Sub ToggleGridlines()
    ' ── Gridline Toggle ───────────────────────────────
    ' Toggles gridlines on/off across all visible
    ' sheets. Detects current state from the active
    ' sheet and offers the opposite action. Reversible
    ' — run again to restore the original setting.
    '
    ' Non-destructive. Only changes a display property.
    ' No data, formulas, or formatting are modified.
    ' ────────────────────────────────────────────────────

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

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim startWS As Worksheet
    Dim currentState As Boolean
    Dim response As VbMsgBoxResult
    Dim count As Long

    ' ── Detect current state ───────────────────────────
    Set startWS = ActiveSheet
    currentState = ActiveWindow.DisplayGridlines

    ' ── Ask the user ───────────────────────────────────
    response = MsgBox( _
        "Gridlines are currently " & _
        IIf(currentState, "visible", "hidden") & _
        " on the active sheet." & vbCrLf & vbCrLf & _
        "Yes = " & IIf(currentState, "Hide", "Show") & _
        " gridlines on ALL visible sheets" & vbCrLf & _
        "No  = Cancel with no changes", _
        vbYesNo + vbQuestion, "Toggle Gridlines")

    If response = vbNo Then GoTo CleanUp

    ' ── Toggle every visible sheet ────────────────────
    count = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Activate
            ActiveWindow.DisplayGridlines = Not currentState
            count = count + 1
        End If
    Next ws

    ' ── Restore original active sheet ─────────────────
    startWS.Activate

    ' ── Report results ─────────────────────────────────
    MsgBox IIf(Not currentState, "Showed", "Hid") & _
           " gridlines on " & count & " sheet(s)." & vbCrLf & _
           "Run this macro again to " & _
           IIf(Not currentState, "hide", "show") & " them.", _
           vbInformation, "Toggle Gridlines"

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

#Detection, not assumption

The macro doesn’t assume gridlines are on or off — it reads the live state from ActiveWindow.DisplayGridlines on whatever sheet you’re currently viewing:

currentState = ActiveWindow.DisplayGridlines

This is a Boolean that returns True if gridlines are visible, False if hidden. By checking at runtime, the macro always offers the opposite action — if gridlines are on, it offers to hide them. If they’re off, it offers to show them. No state tracker needed. No hidden flags. No remembering whether you ran it last week.

Compare this to the Zero to Blank macro, which uses a custom document property to track state across runs. That approach is necessary when the attribute being toggled isn’t easily detectable across sheets (there’s no ActiveSheet.ZerosHidden property). But gridlines have a simple, reliable, single-property check — so we use it.

#One sheet’s state represents the whole workbook

A subtle design choice: the macro reads gridline state from the active sheet only, then applies the toggle to every visible sheet. What if different sheets have different gridline states?

Good question. DisplayGridlines is technically a per-window property, not a per-sheet property. In practice, when you toggle gridlines on one sheet, Excel keeps that setting as you switch tabs — it persists at the window level. But if someone manually set gridlines on some sheets and not others (possible, though rare), the “current state” detection might misrepresent the workbook. The active sheet’s state is a reasonable proxy for “what the user is seeing right now,” which is what they want to change.

If you need per-sheet awareness, you can count the actual gridline state of every sheet before offering the toggle — but for 99% of use cases, the active sheet is a reliable bellwether.

#Why visible-only

If ws.Visible = xlSheetVisible Then

Hidden sheets are hidden for a reason — lookup tables, prior-year data, macro configuration, validation lists. Toggling their gridlines is pointless because the user can’t see them anyway. Skipping them also keeps the macro fast: fewer sheets to activate means less work.

xlSheetVisible is the Excel constant for “normal visible sheet.” We don’t check xlSheetHidden (user-hidden) or xlSheetVeryHidden (VBA-only hidden) — we only process what the user can see. If you later unhide a sheet, run the macro again to pick it up.

#`ScreenUpdating = False` — non-negotiable for sheet cycling

The macro activates each visible sheet in sequence:

For Each ws In ThisWorkbook.Worksheets
    If ws.Visible = xlSheetVisible Then
        ws.Activate
        ActiveWindow.DisplayGridlines = Not currentState
        count = count + 1
    End If
Next ws

Without Application.ScreenUpdating = False, Excel redraws the screen after every ws.Activate. For 28 sheets, the user watches 28 tabs flash by at strobe-light speed. It looks broken. It’s not — the macro is working fine — but it feels wrong and some users will hit Escape in panic.

ScreenUpdating = False freezes the display during the loop. The user sees one brief pause, then the MsgBox. Clean.

After the loop, startWS.Activate returns the user to whatever sheet they were on before running the macro. Without this step, they’d land on the last sheet in the workbook — disorienting, especially if the last sheet is a notes tab or a hidden sheet that was unhidden by another macro.

#No `Calculation` toggle

Unlike macros that write to cells (which trigger formula recalculation cascades), this macro only changes a window display property. No cell values change, no formulas fire, no recalculation happens. Calculation = xlCalculationManual is unnecessary overhead.

The only state management needed is ScreenUpdating = False, and it’s restored in the CleanUp: label even if an error occurs mid-loop.

#The message box tells you what to do next

A silent toggle is a mystery. Did it work? Which direction did it go? The MsgBox resolves both:

Showed gridlines on 12 sheet(s).
Run this macro again to hide them.

The first line confirms the action. The second line tells the user how to undo it — no memorization required. If the user hides gridlines for a partner review on Tuesday and needs them back for data entry on Wednesday, the MsgBox on Wednesday reads:

Hid gridlines on 12 sheet(s).
Run this macro again to show them.

The action and the instruction both invert. The user never has to think about “which state am I in?”

#Adapt It

Keep a specific sheet’s gridlines unchanged. The Cover sheet often looks better without gridlines — it’s a title page with centered text, not a data grid. Add a skip condition:

If ws.Visible = xlSheetVisible Then
    If ws.Name = "Cover" Then GoTo NextSheet
    ws.Activate
    ActiveWindow.DisplayGridlines = Not currentState
    count = count + 1
End If
NextSheet:

Wrap multiple exclusions in a Select Case block for readability:

Select Case ws.Name
    Case "Cover", "TOC", "Dashboard"
        GoTo NextSheet
End Select

Apply to the active sheet only. If you want a quick single-sheet toggle without the full macro, replace the loop with one line:

ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

No variables, no MsgBox, no loops. Ideal for assigning to a keyboard shortcut (Ctrl+Shift+G) in your Personal Macro Workbook.

Always hide, never show. If you only ever want to hide gridlines (for print-ready presentation) and never show them, remove the state detection and the MsgBox question. The macro becomes:

Sub HideAllGridlines()
    Dim ws As Worksheet, startWS As Worksheet
    Set startWS = ActiveSheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Activate
            ActiveWindow.DisplayGridlines = False
        End If
    Next ws
    startWS.Activate
    Application.ScreenUpdating = True
    MsgBox "Gridlines hidden on all sheets.", vbInformation
End Sub

Store in your Personal Macro Workbook. This macro is most useful when it’s available in every file you open:

Press Alt+F11 to open the VBA editor
In the Project Explorer, find VBAProject (PERSONAL.XLSB)
If you don’t see it, record a dummy macro (View → Macros → Record), store it in “Personal Macro Workbook,” then stop recording
Insert a Module and paste the code
Replace ThisWorkbook references — there are none in this macro, so it works as-is on any ActiveWorkbook
Assign a keyboard shortcut: Alt+F8 → select ToggleGridlines → Options → Ctrl+Shift+G

Add it to your Quick Access Toolbar. Click the QAT dropdown → More Commands → Choose commands from: Macros → ToggleGridlines → Add. Now it’s one click from any workbook.

See also: Zero to Blank — the companion toggling macro that hides zero values using a custom number format. Gridlines and zeros together account for 80% of “why does this schedule look cluttered?” Run both before printing or presenting.

Download the sample workbook (10 sheets — trial balance, schedules, depreciation, and notes with mixed gridline visibility)

xlsx 12 KB