VBA code to auto sort table based on any change within a specific column

[duteberta]

Given: Table Name = "MASTER"
Given: Column where change would trigger macro = "Status" (currently column 3 of table)

Given: Sort Macro =

Sub SortTable()

    Dim iSheet As Worksheet
    Dim iTable As ListObject
    Dim iColumn As Range
   
    Set iSheet = ActiveSheet
    Set iTable = iSheet.ListObjects("MASTER")
    Set iColumn1 = Range("MASTER[Status]")
    Set iColumn2 = Range("MASTER[CloseD]")
    Set iColumn3 = Range("MASTER[C1]")
   
    With iTable.Sort
   
      .SortFields.Clear
            .SortFields.Add Key:=iColumn1, Order:=xlAscending
            .SortFields.Add Key:=iColumn2, Order:=xlDescending
            .SortFields.Add Key:=iColumn3, Order:=xlAscending
            .Header = xlYes
            .Apply
        End With

End Sub

+++++++

Question: How do I write VBA code to trigger the above macro? I know the macro works because I've tested it. However I cannot get this trigger based on cell change in column 3.

Context: I already have another script running on the same worksheet that I'm worried is conflicting with what I'm attempting to do....

++++

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tbl             As ListObject
    Dim rngCell         As Range
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    On Error Resume Next
    Set tbl = Target.Worksheet.ListObjects(1)
    If tbl Is Nothing Then Exit Sub
    If tbl.ListRows.Count = 0 Then Exit Sub
    
    Set rngCell = Application.Intersect(tbl.ListColumns("X").DataBodyRange, Target)
    If rngCell Is Nothing Then Exit Sub
    On Error GoTo 0
    
    tbl.ListColumns("X").DataBodyRange = ""
    rngCell.Value = 1
End Sub

++++

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added tags (vba & rich) for you this time.

I'm not sure that you have given us enough information for a full answer.

First code refers to table columns "CloseD" and "C1" but they are not included in your sample **

Second code refers to ListObjects1 but we don't know if that is the same or different to the .ListObjects("MASTER") referred to in the first code.
We don't know what ListColumns("X") in the second code is.

** When providing sample data I suggest that you investigate XL2BB to make it easier for helpers by not having to manually type out sample data to test with.

 

[duteberta]

OK I will re-submit question with the XL2BB- thanks for the suggestion

 

[duteberta]

TEST-vba.xlsm
	B	C	D	E	F	G
3		2024	2 PEND L	Mark Perrine	Christy, Evelyn	04/12/24
4		2024	5 ACT-SELL	Lonnie Hassler	Ireland, Brenda	04/25/24
5		2024	6 PRE-LIST	Darlene Perrine	Christy, Evelyn	05/28/24
6		2024	6 PRE-LIST	Mark Perrine	Blakemore, Dickie	05/03/24
7		2024	6 PRE-LIST	Mark Perrine	Buffum, Stanley G	05/03/24
8	◉	2024	6 PRE-LIST	Lonnie Hassler	Chew, Tad R	05/03/24
9		2024	6 PRE-LIST	Mark Perrine	Henry, Paul D	05/03/24
10		2024	6 PRE-LIST	Mark Perrine	LaPlant, June M	05/03/24
11		2024	8 FUTURE	Mark Perrine	Blakemore, Dickie J
12		2024	8 FUTURE	Mark Perrine	LaPlant, June M
13		2024	9 FUTURE L	Darlene Perrine	Woods, James	12/31/24
14		2024	9 FUTURE L	Darlene Perrine	Zacchaeus, Josh	02/08/24
15		2024	9 FUTURE L	Lonnie Hassler	Chew, Tad R
16		2024	9 FUTURE L	Mark Perrine	Emerson, Steve
17		2024	9 FUTURE L	Darlene Perrine	Fillenwarth, Joseph
18		2024	9 FUTURE L	Mark Perrine	Good, Ursula E
19		2024	9 FUTURE L	Darlene Perrine	Henry, Paul D
20		2024	9 FUTURE L	Mark Perrine	Hogue, Rick E
21		2024	9 FUTURE L	Darlene Perrine	Humpherys, Jeffrey
22		2024	9 FUTURE L	Lonnie Hassler	Ireland, Mark
23		2024	9 FUTURE L	Darlene Perrine	Licari, Connie
24		2024	9 FUTURE L	Lonnie Hassler	Olin, Franklin
25		2024	9 FUTURE L	Lonnie Hassler	Rector, Katlyn
26		2024	9 FUTURE L	Lonnie Hassler	Wert, Dee
27		2024	9 FUTURE L	Darlene Perrine	West, Nikki
28		2024	9 FUTURE L	Darlene Perrine	Wurtz, Larry
29		2050	EXP/WITH/TERM	Lonnie Hassler	Zakasourus, Tester Z	12/31/24
30		2024	SOLD	Lonnie Hassler	Hancock, Deborah J	03/15/24
MASTER

Cell Formulas
Range	Formula
C30	C30	=IF([@CloseD]>0,YEAR([@CloseD]),#REF!)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:G30	Expression	=IF(AND($B3=1,OR($D3="1 PENDING",$D3="2 PEND L",$D3="3 OFFERED")),TRUE, FALSE)	text	NO
C3:G30	Expression	=IF(AND($B3=1,OR($D3<>"1 PENDING",$D3<>"2 PEND L",$D3<>"3 OFFERED")),TRUE, FALSE)	text	NO
B3:B30	Expression	=$B3=1	text	NO
B3:G30	Expression	=$D3="1 PENDING"	text	NO
B3:G30	Expression	=$D3="2 PEND L"	text	NO
B3:G30	Expression	=$D3="3 OFFERED"	text	NO
B3:G30	Expression	=$D3="4 ACT-BUY"	text	NO
B3:G30	Expression	=$D3="5 ACT-SELL"	text	NO
B3:G30	Expression	=$D3="6 PRE-LIST"	text	NO
B3:G30	Expression	=$D3="7 TOM"	text	NO
B3:G30	Expression	=$D3="8 FUTURE"	text	NO
B3:G30	Expression	=$D3="9 FUTURE L"	text	NO
B3:G30	Expression	=$D3="SOLD"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D3:D30	List	1 PENDING, 2 PEND L, 3 OFFERED, 4 ACT-BUY, 5 ACT-SELL, 6 PRE-LIST, 7 TOM, 8 FUTURE, 9 FUTURE L, SOLD, EXP/WITH/TERM
E3:E1048576	List	Mark Perrine, Darlene Perrine, Lonnie Hassler

 

[duteberta]

I am not concerned with the Conditional Formatting - only the VBA code for sorting on cell change.

 

[Peter_SSs]

Thanks for the XL2BB but unfortunately no headings so we still don't really know what column is what. Could we have XL2BB again but include headings and all columns relevant to any of the formulas or conditional formatting?

Also, can you confirm how the values in the "trigger column" (Status) will be changed? (eg Manual Entry, Changed by vba code, changed as a result of a formula recalculation)

 

[duteberta]

Yikes I just realized the headers didn't come over. The trigger would be the dropdown in the "Status" column selecting any value.

 

[Peter_SSs]

Thanks. Unless you already have a Worksheet_Change event code in the code module of sheet 'MASTER', all you should need is this in the 'MASTER' worksheet's module.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("MASTER[Status]")) Is Nothing Then SortTable
End Sub

If you do already have a Worksheet_Change event code in the code module of sheet 'MASTER', then please post that code (remembering my comment at the beginning of post 2)

 

[duteberta]

I think my problem is that I have two codes running under the "Private Sub Worksheet_Change(ByVal Target As Range)" in the same worksheet and I'm not sure how to arrange them so they do not conflict with each other.

1. One is a currently working script: Changing row to values (gets rid of underlying functions in row cells)
2. And the second one is what I'm trying to implement- the auto sort one dropdown change feature

Mayne this is just impossible to do. I'll just have to manually run SORT macro when I need.

Thanks for the help anyway.

 

[Peter_SSs]

Mayne this is just impossible to do.

It should not be impossible.

I have two codes running under the "Private Sub Worksheet_Change(ByVal Target As Range)"

As I said in my previous post ...

If you do already have a Worksheet_Change event code in the code module of sheet 'MASTER', then please post that code (remembering my comment at the beginning of post 2)

Once I can see your existing code (Changing row to values), I can investigate how to combine the two.