Excel MS Excel 2010 -- Automatically resorted tables when new row is added

T

trishcollins

Joined
Mar 21, 2012
Messages
2
Reaction score
0
When I try to run this code to automatically sort a table on a worksheet deactivate, it won't let me leave the worksheet. The purpose is to have the table automatically sort on deactivate to keep together the 'Divisions" as they are part of a dependent drop-down list and must be kept in order. I don't want this to run after every worksheet change, as someone might add in several rows before they go back to the input sheet, and it would be very distracting to have the list automatically resort after they make each cell entry. Any suggestions?

BTW, the code works fine if I just put in into a module and call it something other than Private Sub Worksheet_Deactivate()

Code: 
Private Sub Worksheet_Deactivate()'
' Custom_Sort_Locations_List Macro
'
'
    ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort. _
        SortFields.Add Key:=Range("Locations[RCMP Division]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort. _
        SortFields.Add Key:=Range("Locations[PROV]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort. _
        SortFields.Add Key:=Range("Locations[City]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort. _
        SortFields.Add Key:=Range("Locations[Site Address]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort. _
        SortFields.Add Key:=Range("Locations[RCMP Location ID]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("RCMP Locations").ListObjects("Locations").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Trish :)
 
Last edited:
Why not create a button that the user can click after entering all the rows? This button can then run this code.
 
DJDJDJDJ said:
Why not create a button that the user can click after entering all the rows? This button can then run this code.
Click to expand...

I actually got the worksheet_deactivate() to work. Turns out I need to turn off Application screen updating. It appeared I wasn't leaving the sheet otherwise, when in fact, I was.

It's all working now.

Thanks....Trish :)
 
Back
Top