Once a worksheet has been protected in Excel, to prevent formulas from being changed, for example, then a number of useful functionalities are no longer available. One of the most frustrating, for many people, is that spell checking is greyed out on the Excel ribbon.

In order to make spell checking available, the only work-around is to write a macro which will temporarily un-protect the sheet, run the spell check and then re-protect the sheet. The macro itself is simple, but where it starts to get complicated is when different files, perhaps in a corporate environment, have different passwords. In addition, users need the flexibility to spell check just a selection, an entire worksheet or indeed all worksheets in an Excel workbook.

The example Excel macro-enabled file (link at the bottom of this post) contains the code to create a custom menu-bar in the Add-ins section of the Excel ribbon. The menu will be loaded when the file is opened and removed when the file is closed. The code creates menu items to check a selection of the active sheet, an entire sheet and all sheets for two departments, Finance and Procurement. There are two example sheets in the file for Finance and Procurement, which have been locked with two different passwords “passf” for Finance and “passp”. Normally two different passwords would not be used in same file, this is done purely for the sake of the example.

Error handling provides users with a message if they try to run spell-checking on an Excel file using the wrong menu item (e.g. running a Finance spell check on a Procurement file or a Procurement spell check on a Finance file). The process could be streamlined if some consistent indication could be given of which department files originated from, e.g. using a named range in a hidden sheet set to Finance or Procurement.

Link to code example.

Advertisements