NM123
12-23-2012, 10:17 PM
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.
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
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.
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