Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Inserting and Deleting Rows in a Protected Worksheet

Please Note: This article is written for users of the following Microsoft Excel versions: 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Inserting and Deleting Rows in a Protected Worksheet.

Beginning in Excel 2000, Microsoft added the capability to define what the user can do in a protected worksheet. Using the check boxes in the Protect Sheet dialog box, you can choose a wide variety of allowed actions. (See Figure 1.)

Figure 1. The Protect Sheet dialog box.

One of the actions allowed in the Protect Sheet dialog box is inserting and deleting rows and columns in a protected worksheet. This is a great feature for some types of worksheets. But there is something odd in how this feature actually works.

Let's say you create a worksheet and you protect it. In doing so, you indicate that it is OK for people to both insert and delete rows in the protected worksheet. When someone tries to insert a row in the protected worksheet, it inserts just fine. If someone tries to delete a row—even the row they just inserted—the action is not allowed; it cannot be done. What gives? You indicated in the Protect Sheet dialog that people could delete rows, but still Excel doesn't allow you to do so.

The reason seems to be tied to how Excel implements worksheet protection. Remember that protection is applied to all cells that are formatted as Locked on the Protection tab of the Format Cells dialog box. If any cell in a row you try to delete is formatted as Locked, then worksheet protection won't allow you to delete the row. If all the cells in the row have the Locked check box cleared, then the row can be successfully deleted. (See Figure 2.)

Figure 2. The Protection tab of the Format Cells dialog box.

Interestingly, the user cannot delete a row that they added because the added row inherits the cell formatting of the row above it. So, if there are any locked cells in the row above the row inserted, then the inserted row cannot be deleted as long as the worksheet is protected.

This behavior, of course, is not terribly useful for any worksheet developer that really wants people to have the ability to delete rows in a worksheet, regardless of whether cells in the rows are locked. If you are one of those people, then the only thing you can do is create a macro that does the deletion for you. The macro must accomplish the following steps:

  1. Unprotect the worksheet
  2. Delete the row
  3. Protect the worksheet again

These three steps are relatively easy to perform, using techniques described in other issues of ExcelTips. To make the macro useful, you will probably want to add code that ensures the user isn't trying to delete rows you don't want them to delete (headings, totals, etc.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3004) applies to Microsoft Excel 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Inserting and Deleting Rows in a Protected Worksheet.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.