Insert Row on a table in a protected sheet.

**siroos12** · 12-11-2013, 04:38 PM

Good day all,

I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".

I tried the below macro to unlock the sheet, insert row and lock the sheet again:

Please Login or Register  to view this content.

So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.

Best regards.

**siroos12** · 12-11-2013, 05:50 PM

I just drilled a bit and achieved this solution:

Please Login or Register  to view this content.

so this code will lock my worksheet by selecting any cell in range "a1:r65353". in this way the work book will be lock all the times.
to insert and delete rows i used these codes:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

these two codes will un-protect the sheet, insert or delete the row and by selecting any other cell the first code will lock the sheet again.
any other idea?

**dr01allen** · 07-08-2015, 09:14 AM

This also works:

Sub InsertRow()

Dim objList As ListObject
Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject
Dim loRowNum1 As Long
Dim loRowNum2 As Long
Dim tblCell As String

Set SelectedCell = ActiveCell

'Determine if ActiveCell is inside a Table
On Error GoTo NoTableSelected
TableName = SelectedCell.ListObject.Name
Set ActiveTable = ActiveSheet.ListObjects(TableName)
On Error GoTo 0

tblCell = SelectedCell.Address
Set objList = SelectedCell.ListObject
loRowNum1 = objList.Range.Row
loRowNum2 = SelectedCell.Row

'Do something with your table variable (ie Insert a row below the selected cell)

'This sheet is protected but doesn't use a password. If one is added, see the unprotect method for details. Same comment for the protect statement later.
ActiveSheet.Unprotect

' Add row below selected row
ActiveTable.ListRows.Add (loRowNum2 - loRowNum1) + 2
ActiveSheet.Range(tblCell).Activate

ActiveSheet.Protect

Exit Sub

'Error Handling
NoTableSelected:
MsgBox "There is no Table currently selected!", vbCritical

End Sub

To delete the row containing the selected cell, substitute:
Selection.ListObject.ListRows(loRowNum2 - loRowNum1 + 1).Delete

for the statements below the 'add row' comment.