Using Macro to sort without clicking on macro button

[dd]

Hi,
I have four columns of data. I want to sort Col C in ascending order,
then Col A in ascending order. When someone enters a name in Col C,
they would have to click on the macro button to resort the data. So
instead of clicking on the macro button, is there a way to
automatically resort the data after it is entered in Col C or Col A.
Thanks for your help.

 

[Gord Dibben]

You could use change event code in the worksheet but this negates the "undo"
feature so if you have made a mistake, your sort has taken place as soon as you
hit the ENTER button so makes it hard to troublesheet for errors in data entry.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
If Intersect(Range(Target(1).Address), _
Range("A:A, C:C")) Is Nothing Then Exit Sub
Set OldCell = Target
Application.EnableEvents = False
Columns("C:C").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
OldCell.Select
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP

 

[dd]

Hi Gord,

Thanks for the quick reply but after I enter some text in Col C and
press enter key, nothing really happens, only Col C and Col D is
highlighted but none sorted.

I pasted the code in the sheet tab, view code.

The data actually begins on Row5 so I tried changing the A2 to A5 and
C2 to C5.

Any help is appreciated. Thanks again.

 

[Gord Dibben]

I don't know what to say.

Works for me as written.

Sorts column C then Column A if data entered in column C or A and leaves the
active cell at last point of entry.

I would make one small change............

OldCell.Select becomes

OldCell.Offset(1, 0).Select would leave active cell at cell below last point of
data entry.

Did you copy/paste the code from my posting?


Gord