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

[muffins]

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.

 

[Ekim]

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]

Thanks for this Mike.
I was having this hassle with a worksheet of mine, and this works perfectly.

Cheers

 

[generalgort]

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]

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]

I've actually noticed while testing this that I can actually update the comments in the cells.

 

[rjbec]

Hi,

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

 

[thedoccontroller]

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.