Help with Existing VBA Code

[Mista_sav]

Hi Team,

I have the following VBA Code that auto sorts and works great! The only issue is it wont work once i protect the sheet. I have one column locked and when i protect the sheet i allow sort but it wont work once i protect the sheet

Is there anything i can add to the code that will work?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("K:K")) Is Nothing Then
Range("K5").Sort Key1:=Range("K300"), _
Order1:=xlAscending, Header:=xlDisbursed, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

 

[Micron]

maybe

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("K:K")) Is Nothing Then
   ActiveSheet.Unprotect
   Range("K5").Sort Key1:=Range("K300"), _
   Order1:=xlAscending, Header:=xlDisbursed, _
   OrderCustom:=1, MatchCase:=False, _
   Orientation:=xlTopToBottom
   ActiveSheet.Protect
End If

End Sub

although I would not use Resume Next here. I'd use an error handler instead in case protection was left off. If sheet needs to be password protected you can add that. Google Excel vba unprotect sheet. Please use code tags (vba button on posting toolbar) to maintain code indentation and readability.

 

[Mista_sav]

maybe

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Not Intersect(Target, Range("K:K")) Is Nothing Then
   ActiveSheet.Unprotect
   Range("K5").Sort Key1:=Range("K300"), _
   Order1:=xlAscending, Header:=xlDisbursed, _
   OrderCustom:=1, MatchCase:=False, _
   Orientation:=xlTopToBottom
   ActiveSheet.Protect
End If

End Sub

although I would not use Resume Next here. I'd use an error handler instead in case protection was left off. If sheet needs to be password protected you can add that. Google Excel vba unprotect sheet. Please use code tags (vba button on posting toolbar) to maintain code indentation and readability.

Perfect thank you so much mate. Will take that on board too. Appreciate the advice