When I tried to insert another worksheet in an Excel workbook today, I found that the option to insert a new blank worksheet was grayed out when I clicked on Insert and selected Sheet.
When I tried another means of adding a new sheet by clicking on the plus sign at the right side of the list of sheets in the worksheet to add the sheet, I saw an alert message stating "Workbook is protected and cannot be changed."
The file was an Excel Macro-Enabled Workbook (.xlsm) file. I encountered
a similar problem in the past with Excel for Mac 2011 with a .xlsx file -
see Unprotecting an Excel
workbook when the password is not known - so I employed a similar
procedure, since .xlsm files are also
Office Open XML (OpenXML),
zipped,
XML-based files. I changed the file extension from .xlsm to .zip then
double-clicked on the file in the
Finder to extract the contents of the zip file into a subdirectory within
the one where the .zip file was located. I then edited the
workbook.xml file in the xl subdirectory where the
extracted files were stored with the
vi text editor.
You can also use the
TextEdit
application that comes with the OS X operating system to edit the
file. I searched for workbookPassword and saw the
following:
</mc:Choice></mc:AlternateContent><workbookProtection workbookPassword="F91C" lockStructure="1" />
I deleted the F91C, so that I had
workbookPassword="", and saved the file. I then recompressed
the contents of the directory into which all the files in the .zip file
were extracted using the Zipdir
Python script, since using the Finder or the command-line interface (CLI)
zip utility that comes with OS X
will not produce a zip file that Excel will be able to open if the .zip file is
renamed to .xlsm.
$ python ~/Documents/bin/zipdir.py MSGRS_2018 MSGRS_2018 $
After I renamed the .zip file to be an .xlsm file, I still saw the option to insert a new sheet greyed out when I chose Insert and Sheet and I still received the same "Workbook is protected and cannot be changed" message when I clicked on the plus sign at the bottom, right side of the Excel window to add another sheet, but I was then able to click on Review, which appears in the menu bar row where Home, Insert, Page Layout, etc. appear, and then click on the Protect Workbook icon to remove the workbook protection.
When I opened the Protect Workbook icon had a dark gray background around it whereas Protect Sheet did not. When I clicked on the Protect Workbook icon, the dark gray background disappeared and, instead, the background then looked like that for the Protect Sheet icon.
I was then able to add a new sheet to the workbook and use the new spreadsheet.
Related articles:
