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:

Sub UnlockInsrtLock()
'
' UnlockInsrtLock Macro
' Unlock the worksheet, Insert a row an lock it again.
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    ActiveSheet.Unprotect
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    ActiveCell.Offset(1, 0).Range("A1").Select
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
End Sub

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("a1:r65353")) Is Nothing Then


' Change the password "" to your password (include the apostrophes)



Const PW = ""
For Each sht In ActiveWorkbook.Sheets
sht.Protect _
Password:=PW, _
 DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
Next sht

End If
End Sub

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:

Sub UnlockInsrtLock()
'
' UnlockInsrtLock Macro
' Unlock the worksheet, Insert a row an lock it again.
'
' Keyboard Shortcut: Ctrl+Shift+i
'
Const PW = ""
    ActiveSheet.Unprotect
    Password = ""
     Selection.EntireRow.Insert Shift:=xlDown
     ActiveSheet.Unprotect
    Password = ""
    
    
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
End Sub

and

Sub UnlockDeleteLock()
'
' UnlockDeleteLock Macro
' Unlock the worksheet, Delet a row an lock it again.
'
' Keyboard Shortcut: Ctrl+Shift+d
'
Const PW = ""
    ActiveSheet.Unprotect
    Password = ""
     Selection.EntireRow.Delete Shift:=xlUp
     ActiveSheet.Unprotect
    Password = ""
    
    
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
End Sub

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.