Sort in a worksheet change event

Xenowyn

New Member
Joined
Jul 22, 2014
Messages
30
So I have the following code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 1 Then
    Columns(1).Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlYes
End If
Application.EnableEvents = True

End Sub

I want to sort the values in column A in ascending order (the header consists of rows 1 and 2 merged) only if a cell in column A is changed. But when I change a cell in column A with this code in place, nothing happens. Any help?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Is the code entered for the worksheet that has your data in it (as it needs to be), or is it entered in a different worksheet or a module ?
 
Upvote 0
Yes, my data is on sheet1 and the above code is in the sheet1 section of the vba interface. Is the logic and syntax of the code itself correct? Also, I inserted a message box into the change event to verify if the event is even firing, yet when I change anywhere on the worksheet there's no message box that pops up, so I'm really stumped.
 
Upvote 0
Your code works for me. The code must be placed in the worksheet object, not in a module as most macros are.
 
Upvote 0
As far as I can tell, it is placed in the worksheet object. I open vba, then on the left side under VBAProject I expand the Microsoft Excel Objects folder, click Sheet1, and this is where the code is placed.
 
Upvote 0
Try this, it eliminates row 1:2 from the sort and says no headers (i.e. row 3 is not a header row)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Column = 1 Then
    With Columns(1)
        With Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
            .Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlNo
        End With
    End With
End If
Application.EnableEvents = True

End Sub
I'm not a big fan of automatic sort routines. As soon as the user makes a typo, the sort routine takes that data/typo away from the view of the user.
 
Upvote 0
Ok now when I use your code the event works. How could I get sort to move the entire row along with the cell in column A?
 
Upvote 0
As far as I can tell, it is placed in the worksheet object. I open vba, then on the left side under VBAProject I expand the Microsoft Excel Objects folder, click Sheet1, and this is where the code is placed.

The key point I think is in your post #3, where your saying that the MsgBox isn't showing which means that the code isn't executed when you change the sheet. Your code works fine for me so there is no problem with it.
It does look like some how you have placed your code in the wrong workbook. Other points to check:
1. If you have renamed the sheets, this might be the problem. Open the VBE, and double click the Sheet that contains your code, in Project Explorer.
In the code window, click in the panel to the left of the line "If Target.Column..........". A large brown dot will appear where you click, and the background for the line also turns brown.
This creates a pause before that line is executed, and it displays the code onscreen. This is very useful for checking that the code is executing. You can turn it off by clicking the brown dot.
2. With pause turned on, close the VBE and return to your data. Change something in the sheet that you have placed the code in, and the VBE screen should display with the "pause" line highlighted.
If it doesn't, then the code isn't in the sheet you think it is. Try all the other sheets until the VBE screen displays (if you can't work out where it is based on the sheet names). When you find it
you can transfer the code to the correct sheet.
 
Last edited:
Upvote 0
Try this
Code:
If Target.Column = 1 Then
    With Columns(1)
        With Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)).[COLOR="#FF0000"]EntireRow[/COLOR]
            .Sort key1:=Range("A3"), order1:=xlAscending, Header:=xlNo
        End With
    End With
End If
Application.EnableEvents = True
 
Upvote 0
Thanks mike, that works well. I understand what you meant about not liking automatic sort routines. I need to keep the automatic sorting, but I put it in a close workbook event instead so that it will allow the user to see mistakes and fix them before the data gets whisked away.

And Hercules, as for the issue of the event not firing, I closed Excel then reopened it and it began working... So I'm still not sure what the issue was (the sheet does have the same name).
 
Upvote 0

Forum statistics

Threads
1,222,103
Messages
6,163,947
Members
451,867
Latest member
csktwyr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top