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