Macros & shared workbooks


Board Regular
Nov 28, 2010
All, thank you for taking a look at this question. I have read some past questions and answers here on Mr.Excel from 2008 about how marcos/vba will not run on a worksheet(s) protection when shared.

Here is a past comment,

I discovered this characteristic about Excel 2007 shortly after our office converted to Office 2007.

The workbooks I had built in Excel 2003, when converted to .xlsm, would not enable macros and wouldn't offer the user the option to do so. I researched the issue online and someone thought that worksheets and workbooks could not be protected or Excel 2007 won't enable macros.

After working with Excel 2007 for a while, I found that Excel 2007 doesn't care if the worksheets are protected, with or without a password, but it WILL NOT enable macros in a workbook that is password protected on open. Period! Won't give you the option to enable either, even if you place the workbook in a "trusted location." The workbook can be protected when opened, just not password protected. I think it has something to do with a virus scan on open.

Like you, I need my workbooks to be protected so users don't delete, add, or re-arrange the sheets. Once the workbook is open, you can apply password protection to the workbook itself and everything works OK because the macros have already been enabled. I now make sure that workbook protection is removed on close and then reset by the user (click this button to show sheets) after its opened.

I can't post attachments here, but I will post a sample workbook for you at:

Has anyone figure out a way since 2008 to protect worksheets/workbooks and then share the workbook with others in the office?

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics