Insertion and Deletion of rows in a Protected excel sheet

[NM123]

Hi All,

Can any one guide me on inserting and deleting rows in a protected worksheet. I have attached the sample sheet. The greyed cells are need to be protected and I need to add or delete rows(if any changes required in the sheet later on). I have also attached the sample code i am using. This code is executed in last as there are some other activities to be completed before running the "Protection" Code. The code allows me to insert but i am not able to delete rows from the Test Case Sheet after the sheet is protected.


[VBA]
Option Explicit
Dim path As String
Dim wbList() As String
Dim wbCount As Integer
Dim intColNames As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim strFindOnBoard As String
Dim strFindCategory As String
Dim strFindSubCategory As String
Dim str() As String

Sub Protection()
'/* Sub Routine to protect specific cells */
'Sheet1.Protect contents:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True
For i = 1 To Sheet1.UsedRange.Rows.Count
For j = 1 To Sheet1.UsedRange.Columns.Count
If Sheet1.Cells(i, j).Interior.ThemeColor = -4142 Then
Sheet1.Cells(i, j).Locked = False
End If
Next
Next
Sheet1.Protect contents:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True

End Sub
[/VBA]

[patel]

Don't protect your sheet before deleting rows

[NM123]

Hi Patel,
If i am unprotecting the sheet, the greyed cell will be unlocked too. There might be a chance of data getting changed or removed.So dont want to take any chance for data editing but would like to provide the user to insert or delete rows as per their need.

[patel]

With unprotected sheet you can hide the gray rows, if I remember well you can not delete hidden rows

[david000]

I believe you need the cells unlocked for deleting rows and sorting. I normally keep my formulas locked and unlock the entire sheet for users to modify.


[VBA]
Sub Lock_Formulas_Only()
On Error GoTo Msg
With ActiveSheet.Cells
.Locked = False
MsgBox "Cells are Unlockled."
.SpecialCells(xlCellTypeFormulas, 23).Locked = True
End With
Exit Sub
Msg:
MsgBox Err.Description
End Sub
[/VBA]

Test every scenario you need (there are more I didn't add below)

[VBA]
Sub Protect_Unprotect() 'Toggle Protection Mode
Dim wSheet As Worksheet
Dim Password As String * 0
Application.ScreenUpdating = False
Password = ""
For Each wSheet In Worksheets
With wSheet
If .ProtectContents = True Then
.Unprotect Password
Else
.Protect Password, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowSorting:=True, _
AllowFormattingCells:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True, _
AllowInsertingRows:=True, _
AllowDeletingRows:=True
End If
End With
Next wSheet
Application.ScreenUpdating = True
End Sub

[/VBA]

[NM123]

Hi David,
Thanks for your response. I did tested your code snippet which is working fine for a blank worksheet(as i have attached in my post earlier). As mentioned there are some other activities which is done before implementing the protection to the sheet. The sheet is a test case template in which i used fill the cells through macro, then execute the Protection macro(your code). The doesnt get protected, the fieldsa are editable.I have to click on the Protection button in the toolbar to complete the action.
Please let me know if you are not clear with my requirements.

[david000]

You need to unlock all the cells first, then lock the ones you need to keep locked. Protection doesn't allow you to delete rows after you activate it, even if the entire row is obviously blank.

[VBA]
Sub Protection()
Dim i As Integer
Dim j As Integer
Dim rng As Range

'/* Sub Routine to protect specific cells */
'Sheet1.Protect contents:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True

Sheet1.Cells.Locked = False 'Unlock all the cells

For i = 1 To Sheet1.UsedRange.Rows.Count
For j = 1 To Sheet1.UsedRange.Columns.Count
If Sheet1.Cells(i, j).Interior.ThemeColor = -4142 Then
Sheet1.Cells(i, j).Locked = True 'lock only the cells in grey.

End If
Next
Next
Sheet1.Protect contents:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True
End Sub
[/VBA]

[NM123]

Thanks David for your response.
I have to manually click on the "Unprotect Sheet" button after executing the macro>then again click on "Protect Sheet" button.Pop-up comes, there i need to click on the check box for Delete Rows>Click on OK. Now the sheet is protected again with both Insert and Delete of Rows option enabled.