VBA - Lock rows in a table while still being able to filter/sort.

[cmcev]

Hi,

I'm currently using the following code to lock Row's A:P. I have a dropdown in column R to select 'Yes'. Once this is selected I have conditional formatting changing the row to grey to show it shouldnt be edited. However in doing so, once the rows are locked I can no longer filter or sort the table.

Any advice would be great.

Thanks,
C

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("E1")
End If
If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Yes"
Range("A" & Target.Row & ":P" & Target.Row).Locked = True
UserInterfaceOnly = True
AllowSorting = True
AllowFiltering = True
Case Else
Range("A" & Target.Row & ":P" & Target.Row).Locked = False
End Select
End Sub

 

[Fluff]

Duplicate to: Lock rows in a table while still being able to filter/sort.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.