CODEWITHSUNDEEP
vbaexcel
user1342164
user1342164

Reputation: 1454

Conditional Lock Cell , unable to sort

I am trying to write a macro that will lock any cell greater than 0. When I run the code below it works but locks the 1st row where I have a drop down arrow that does sorting and number filters. Is there a way to add to this code so that the first row wont be locked?

Sub Test()
    Dim Cell As Range
    Dim MyPlage As Range

    With ThisWorkbook.ActiveSheet
        .Unprotect
        .Cells.Locked = False
        Set MyPlage = .Range("J2:AA1074")
        For Each Cell In MyPlage
            If Not IsError(Cell) Then
                If Cell.Value > "0" Then
                    Cell.Locked = True
                End If
            End If
        Next
        .Protect
    End With

End Sub

Upvotes: 0

Views: 165

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149315

The most simplest was is to define your range which doesn't include the Top Row :)

Change

.Range("J2:AA1074")

to

.Range("J3:AA1074")

Also, Instead of looping through every cell in the range and checking if that cell has an error or not, you can directly use SpecialCells. For example (TRIED AND TESTED)

Sub Sample()
    Dim Cell As Range, MyPlage As Range, FinalRange As Range

    With ThisWorkbook.ActiveSheet
        .Unprotect
        .Cells.Locked = False

        On Error Resume Next
        Set MyPlage = .Range("J3:AA1074").SpecialCells(xlCellTypeConstants)
        On Error GoTo 0

        If Not MyPlage Is Nothing Then
            For Each Cell In MyPlage
                If Cell.Value > 0 Then Cell.Locked = True
            Next
        End If

        .Protect DrawingObjects:=True, _
                 Contents:=True, _
                 Scenarios:=True, _
                 AllowFiltering:=True, _
                 AllowSorting:=True

        .EnableSelection = xlUnlockedCells
    End With
End Sub

To ensure that Autofilter and Sorting works, specify it in .Protect as I have done above.

Before you run the above code, you also need to take one extra step.

  1. Unprotect the worksheet if it is already protected
  2. Under Review Tab, click on "Allow Users to Edit Ranges"
  3. Add "New" range
  4. Select the range you want allow users to sort

Screenshot

enter image description here

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can add following code to the Sheet module (change Range("J1:AA1") to the range with your autofilter):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.Intersect(Target, Range("J1:AA1")) Is Nothing Then
        ActiveSheet.Protect
    Else
        ActiveSheet.Unprotect
    End If
End Sub

enter image description here

Upvotes: 1

Related Questions