delete/insert row(s) in a protected worksheet?

muffins

Board Regular
Joined
Jun 18, 2002
Messages
86
Hi everyone,

I understand once a worksheet is protected, we are unable to make any changes to locked cells or to delete/insert rows.

Is there a possiblity to allow deletion/insertion of rows in a protected worksheet?

Many thanks in advance.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Yes, you are right. Once a worksheet is protected, you are unable to make any changes to locked cells or to delete/insert rows. However, it is possible to allow this.

To allow data input in a protected worksheet, you must "unlock" a cell or a range of cells before you protect the worksheet.

Select the cells that you want to input data -

a) Go to the Format menu/Cells/Protection tab
b) Uncheck "Locked"
c) OK

Now protect the worksheet.

Before we start, open a new workbook and puts some data in sheet1 rows A2:A10 (e.g. 10,20,30 etc); similarly puts some data in say sheet3 rows A15:A30. This will be our test data.

You will need three macros. To access the VBA editor: Alt-F11:

1. In the ThisWorkbook module, copy and paste everything between the lines (note the first line of the macro about changing the password).

---------------------------------------

' Change the password "pass" to your password (include the apostrophes)
Const PW = "pass"

Private Sub Workbook_Open()
For Each sht In ActiveWorkbook.Sheets
sht.Protect _
password:=PW, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
Next sht
End Sub
--------------------------------------------

When you open your worksheet, this macro automatically protects all worksheets (so if you unprotect a worksheet and forget to protect it, the macro will reinstall protection the next time you open the file).


2. In a normal module (while in the VBA Editor, go to the Insert menu, click Module), copy and paste everything between the lines:

-------------------------------------------

Sub insertrow()

' Insert a row
Selection.EntireRow.Insert Shift:=xlDown
End Sub


Sub deleterow()

' Delete a row
Selection.EntireRow.Delete Shift:=xlUp
End Sub

----------------------------------------------

Return to your Excel worksheet (click outside the VBA Editor or click the Excel icon in the top left-hand corner of the VBA Editor).

Save the file and close it.

Now open the file.

The workbook should be protected (check by trying to put some data in a cell).

Go to sheet1, click in row 5 (it doesn't matter what column you are in). Go to the Tools menu/Macro/Macro - select the Insertrow macro, then click Run. A new row should be inserted, and all data moved down one row. To delete a row, click in the row you want to delete and run the deleterow macro.

Regards,


Mike
 

muzashi1963

New Member
Joined
Jul 31, 2011
Messages
4
Thanks for this Mike.
I was having this hassle with a worksheet of mine, and this works perfectly.

Cheers
 

generalgort

New Member
Joined
Jun 5, 2010
Messages
10
Hi Guys,

This nearly solves the problem I have, mine concerns columns rather than rows, so do I just substitute 'column' for 'row', '=xlRight' for '=xlDown' and '-xlLeft' for '=xlUp' in module 2?

My other problem is my spreadsheet has columns which the operator needs to fill in, but must not be able to delete. So what I really need is for the operator to be able to add columns and be able to delete ONLY those columns which they have added, if they delete any of the original columns, other parts of the sheet become meaningless.

I appreciate any help you can give,

Phil.
 

stephanie2497

New Member
Joined
Sep 3, 2015
Messages
2

ADVERTISEMENT

I found an easier way of doing this, however it works best if you don't have many cells that you want protected. I have a worksheet which I really only needed about 25 cells to be protected. I followed the same initial steps...

1. Select entire worksheet using Ctrl + A.
2. Right click and go to Format Cells, Protection, and click the Locked box so it is not checked and click OK.
3. On the worksheet, hold down Ctrl and click the cells you need to be protected.
4. Once you have selected all you want locked, then right click on one of the cells and go to Format Cells, Protection, click the Locked box so it is checked and click OK.
5. Go to the Review tab up top, and click Protect Sheet in the Changes group. Select to allow ALL the options for all users of the worksheet.
6. Entered the password twice that will allow you to unprotect the worksheet and hit OK.

Now my worksheet allows me to make any changes as though it were unprotected, with the exception of being able to edit the few cells I selected - I've tested it and I can add/remove columns and rows, hide/unhide, etc. As mentioned, I didn't have a ton of cells I needed to protect so this could be a painful method if you do. The only other thing I've noticed that it won't allow me to do so far is add or edit comments that I've entered for some of the cells (indicated with the red triangle in the top right corner).
 

stephanie2497

New Member
Joined
Sep 3, 2015
Messages
2
I've actually noticed while testing this that I can actually update the comments in the cells.
 

rjbec

New Member
Joined
Nov 15, 2014
Messages
7
Hi,

When inserting a row, is there a way to add the existing formulas from the row above?
 

thedoccontroller

Board Regular
Joined
Dec 15, 2015
Messages
81
Hi Guys,

This nearly solves the problem I have, mine concerns columns rather than rows, so do I just substitute 'column' for 'row', '=xlRight' for '=xlDown' and '-xlLeft' for '=xlUp' in module 2?

My other problem is my spreadsheet has columns which the operator needs to fill in, but must not be able to delete. So what I really need is for the operator to be able to add columns and be able to delete ONLY those columns which they have added, if they delete any of the original columns, other parts of the sheet become meaningless.

I appreciate any help you can give,

Phil.

Any help on this? I just need the add/delete column ability here. Tried editing the above code, but to no avail.
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top