CODEWITHSUNDEEP
vbaexcel
Ahmad
Ahmad

Reputation: 427

How to run Excel VBA macro continuously?

I'm trying to write a macro to Auto Sort a table in Excel when any value in the table is changed.
This is my VBA code

Sub Macro3(ByVal target As Range)
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table4").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table4").sort.SortFields.Add _
        Key:=Range("Table4[Pts]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table4").sort.SortFields.Add _
        Key:=Range("Table4[GD]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table4").sort.SortFields.Add _
        Key:=Range("Table4[GF]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table4").sort
        .Header = xlYes
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub sort()
    Dim target As Range
    Set target = Range("Table4")
    Call Macro3(target)
End Sub

What should I do to let this macro run continuously each time values in the table change?

Upvotes: 1

Views: 16786

Answers (2)

user4039065
user4039065

Reputation:

A Worksheet_Change event macro will not be triggered when cell values are changed due to a cell's formula recalculating from a change in another worksheet. You need to capture the Worksheet_Calculate event for that.

This is not module code. It belongs on Sheet1's code page. Right-click the Sheet1 tab at the bottom of your workbook and choose View Code. When the VBE opens, paste the following into the into the pane titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Calculate()
    On Error Goto bm_Safe_Exit
    Application.EnableEvents = False
    With ListObjects("Table4").Sort
        With .SortFields
            .Clear
            .Add Key:=Range("Table4[[#All],[Pts]]"), Order:=xlDescending
            .Add Key:=Range("Table4[[#All],[GD]]"), Order:=xlDescending
            .Add Key:=Range("Table4[[#All],[GF]]"), Order:=xlDescending
        End With
        .Header = xlYes
        .Apply
    End With
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Tap Alt+Q to return to your worksheet. If the formulas recalculate, the data in Table4 will be resorted. Events monitoring is temporarily suspended so that the act of sorting does not trigger another calculation event.

Upvotes: 2

eirikdaude
eirikdaude

Reputation: 3256

To have a macro run every time a cell in a table / range changes, you can use events, which you put in the code for the sheet.

In your case, you probably want something like:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, range_of_table) Is Nothing Then
    Call sort
  End If
End Sub

Where range_of_table is the range of the table you've created.

Upvotes: 0

Related Questions