In Excel: Open File ▸ Options ▸ Trust Center ▸ Trust Center Settings ▸ Macro Settings, choose "Disable VBA macros with notification", then reopen the file and click Enable Content on the yellow bar.
VBA macro: Enable Macros in Excel
Sub CheckMacrosEnabled()
Dim msg As String
msg = "Macros are enabled in this session." & vbCrLf & _
"Excel version: " & Application.Version & vbCrLf & _
"Workbook: " & ThisWorkbook.Name
MsgBox msg, vbInformation, "Macro Check"
End SubNothing enables macros from inside VBA — the setting lives in the Trust Center, outside the code. This Sub is a probe instead: if the dialog appears, macros are running; if nothing happens, they are still blocked.
How to run this macro
- Press Alt + F11 to open the VBA editor.
- Insert > Module.
- Paste the code above.
- Press
F5, or close the editor and run it from Developer > Macros. - Save the file as .xlsm so the macro is kept.
Close the workbook, then right-click the file in Windows Explorer, open Properties, tick Unblock, and click OK — skip this only if the file never came from email or a download.
Open Excel and go to File ▸ Options ▸ Trust Center ▸ Trust Center Settings ▸ Macro Settings.
Select "Disable VBA macros with notification" — this is the safe default that still lets you approve a file per workbook.
Optionally add the folder you keep your macro workbooks in under Trusted Locations, so files there open without a prompt.
Reopen the workbook and click Enable Content on the yellow bar.
What this does
Excel ships with VBA macros switched off, and it stays that way until you make a deliberate choice in the Trust Center. There are two separate blocks people run into, and they need different fixes. The first is the ordinary security prompt: a yellow bar reading "Macros have been disabled" appears under the ribbon, and one click on Enable Content lets the file run for good. The second is Mark of the Web — for a workbook downloaded from the internet or received as an email attachment, newer builds of Excel show a red bar instead, with no Enable button at all. That bar cannot be dismissed from inside Excel; you have to right-click the file in Windows Explorer, open Properties, tick Unblock at the bottom of the General tab, and then reopen it. The Trust Center setting governs the yellow bar; the Unblock checkbox governs the red one. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “enable macros in excel file” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
A colleague emails you a reporting workbook. You open it, the red "blocked" bar appears, and none of the buttons on the sheet do anything. Save the attachment to your Documents folder first — macros can never run from the temporary attachment folder. Right-click the saved .xlsm file, choose Properties, tick Unblock, and click OK. Reopen it in Excel and the bar turns yellow; click Enable Content once and the workbook is trusted permanently on that machine. To confirm the macros really are live, paste Sub CheckMacrosEnabled() into a module and press F5 — a dialog naming your Excel version means everything downstream will run. Enabling macros is the first gate every automation runs into: a workbook full of correct VBA does nothing at all while the setting is off, and the symptom — buttons that do not respond, no error message — looks like broken code rather than blocked code. Getting the Trust Center and the Unblock checkbox right once saves diagnosing the same non-bug on every new file that arrives. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. If you take one thing from this page on “enable macros in excel file”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Choosing "Enable all VBA macros" as a permanent setting. It silences the prompt but also removes the one warning you get before a hostile file executes. "Disable with notification" plus Enable Content per workbook gives the same convenience with the protection intact.
- Fighting the red Mark-of-the-Web bar from inside Excel. No Trust Center setting removes it — the block is a property of the file on disk, so it only clears via right-click ▸ Properties ▸ Unblock, or by placing the file in a Trusted Location.
- Enabling macros but saving the file as .xlsx. That format cannot hold VBA at all, so the code is silently discarded on save and the workbook comes back empty of macros next time. Use .xlsm.
- Running the file straight from the email attachment preview. Excel opens attachments in a protected temporary folder where macros never execute; save the file somewhere real first.
Frequently asked questions
How do I enable macros for just one workbook instead of all of them?
Leave Macro Settings on "Disable VBA macros with notification" and click Enable Content on the yellow bar when that specific file opens. Excel remembers the decision for that workbook on that computer and stops asking.
Why is there no Enable Content button on my security bar?
The bar is red, not yellow — that is Mark of the Web, applied to files from the internet or email. Close Excel, right-click the file, choose Properties, tick Unblock on the General tab, click OK, and reopen it.
What is a macro-enabled workbook?
It is the .xlsm file format (or .xlsb for the binary variant, and .xltm for templates). Only these formats store VBA code. Saving a workbook with macros as plain .xlsx deletes the code, and Excel warns you once before it does.
Can my IT department block this?
Yes. Group Policy can lock Macro Settings so the Trust Center options are greyed out, and it can block macros in files from the internet outright. If the options will not change, that policy is why — ask IT for a Trusted Location instead.