PDA

View Full Version : Automatic Sorting while typing



jacque
06-27-2018, 04:19 PM
I would like to sort data while typing. The data is in B7:C20, all other cells are locked. The sort should be based on column B only, which is the date.

I have the following Macro, but it does not work due to the locked cells.

Any help would be appreciated.

PrivateSub Worksheet_Change(ByVal Target As Range)
OnError Resume Next
IfNot Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").SortKey1:=Range("B2"), _
Order1:=xlAscending,Header:=xlYes, _
OrderCustom:=1,MatchCase:=False, _
Orientation:=xlTopToBottom
EndIf
End Sub

Logit
06-27-2018, 10:14 PM
.
If you can adjust your worksheet so the two columns are surrounded by a blank column (one on each side), then this macro will work for you :



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect ""
If Not Intersect(Target, Range("C7:D20")) Is Nothing Then
Range("C7").Sort Key1:=Range("C7"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Application.DisplayAlerts = True
End If
ActiveSheet.Protect "", True, True
End Sub


It is very late here now and my mind is mush. Able to provide the macro above (and existing sample workbook). I'll give this another chance tomorrow
to see if I can create something using just columns B:C in your original macro.

Or if someone else wants to step in .. please do.