Worksheet_Change Event

Topic

New Reply

I have a series of data in columns posted to a new worksheet with VBA and I need to have it automatically sorted.
I have coded a Worksheet_Change Event in the new Worksheet with ;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range(“A2”)
If Not Intersect(Target, VRange) Is Nothing Then _
Run “SortCols”

End Sub

And in the main module, I have the following code:

Sub SortCols()
Dim VRange As Range
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long

Dim TotalRows As Long
Dim WsSource As Worksheet
Set WsSource = Worksheets(“Kintana for Neg CU Adjmt”)
WsSource.Activate
Application.ScreenUpdating = False
TotalRows = Application.CountA(Range(“A:A”)) – 1

Worksheets(5).Range(“A2:D” & TotalRows).Sort _
Key1:=Range(“C2”), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
End Sub

Whenever the columns are filled with data the Worsheet _Change event does not trigger the Run SortCols

Any Ideas?

Reply | Quote

Replies

Your code runs SortCols only when the user modifies cell A2, because of the lines

Set VRange = Range(“A2”)
If Not Intersect(Target, VRange) Is Nothing Then _
Run “SortCols”

Since columns A:D should be sorted on column C, you must run SortCols whenever a cell in column C is changed:

Set VRange = Range(“C:C”)

Reply | Quote

Your code runs SortCols only when the user modifies cell A2, because of the lines

Set VRange = Range(“A2”)
If Not Intersect(Target, VRange) Is Nothing Then _
Run “SortCols”

Since columns A:D should be sorted on column C, you must run SortCols whenever a cell in column C is changed:

Set VRange = Range(“C:C”)

Thanks Hans

Reply | Quote