VBA Workbook Protection question

[Emmily]

Hi, i have the following parameters for workbook protection. Now if i make this True

, AllowDeletingRows:=True _

then the user can delete all rows. I would like to limit to what they can delete.

So how can i add to this so that the user is able to delete rows for wks "Test1" and Test2" from range "C7:X1000" only?

Public Sub protectSheet(ByRef wks As Worksheet, ByVal strPW_Wks As String)

    wks.Protect _
        Password:=strPW_Wks _
        , DrawingObjects:=True _
        , Contents:=True _
        , Scenarios:=True _
        , UserInterfaceOnly:=False _
        , AllowFormattingCells:=False _
        , AllowFormattingColumns:=True _
        , AllowFormattingRows:=True _
        , AllowInsertingColumns:=False _
        , AllowInsertingRows:=False _
        , AllowInsertingHyperlinks:=False _
        , AllowDeletingColumns:=False _
        , AllowDeletingRows:=True _
        , AllowSorting:=True _
        , AllowFiltering:=True _
        , AllowUsingPivotTables:=False
    
End Sub

 

[rudfaden]

You can set a range. So eg wks is not a worksheet, but a range.

 

[Emmily]

You can set a range. So eg wks is not a worksheet, but a range.

I don't quit follow

 

[rudfaden]

Sorry. I was wrong. You can set the locked pproperty to false for a given range. Then that range will not be locked.

Sub protects()

Range("B9:F30").Locked = False
ActiveSheet.protect Contents:=True
End Sub

 

[Emmily]

Why doesn't this work?

Sub protects()

With Sheets(1)
    With .Range("A1:X3")
        .Locked = False
         .Unprotect Password:="TEST", AllowDeletingRows:=True
    End With
   
End With
End Sub

 

[Emmily]

Ok, i have this code now, but how can restrict the user from deleting rows from A1:X4, but still allow deleting rows from rows 5 onwards.

Sub Test()

Dim pw As String
Dim lRowFrom As Long
Dim lRowTo As Long

lRowFrom = 1
lRowTo = 4

    pw = "TEST"
    With ActiveSheet
        .Unprotect pw
        .Cells.Locked = False
        .Range("A" & lRowFrom & ":X" & lRowTo).Cells.Locked = True
        .Protect pw, _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowInsertingColumns:=True, _
            AllowInsertingRows:=True, _
            AllowDeletingColumns:=True, _
            AllowDeletingRows:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True, _
            AllowUsingPivotTables:=True
    End With
End Sub

 

[Nalani]

how can restrict the user from deleting rows from A1:X4

You are refering to it as a Range. Try refering to Rows

 With ActiveSheet
        .Unprotect pw
        .Cells.Locked = False
        .Rows("1:4").Cells.Locked = True

 

[Emmily]

You are refering to it as a Range. Try refering to Rows

 With ActiveSheet
        .Unprotect pw
        .Cells.Locked = False
        .Rows("1:4").Cells.Locked = True

Nope i tried that, but still if i select rows 1:4 it still allows me to delete the rows.

 

[Nalani]

With my test worksheet, and your code (modified) it works for me.

Sub Test()
Dim pw As String
    pw = "TEST"
    With ActiveSheet
        .Unprotect pw
        .Cells.Locked = False
        .Rows("1:4").Cells.Locked = True
       
        .Protect pw, _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowInsertingColumns:=True, _
            AllowInsertingRows:=True, _
            AllowDeletingColumns:=True, _
            AllowDeletingRows:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True, _
            AllowUsingPivotTables:=True
    End With
End Sub

 

[Emmily]

So when you right click rows 1:4 delete is shaded grey, but from rows 5 onwards the delete is active.

With my test worksheet, and your code (modified) it works for me.

Sub Test()
Dim pw As String
    pw = "TEST"
    With ActiveSheet
        .Unprotect pw
        .Cells.Locked = False
        .Rows("1:4").Cells.Locked = True
       
        .Protect pw, _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowInsertingColumns:=True, _
            AllowInsertingRows:=True, _
            AllowDeletingColumns:=True, _
            AllowDeletingRows:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True, _
            AllowUsingPivotTables:=True
    End With
End Sub