Excel Forum

Code: Select all

Private Sub Worksheet_Calculate1()
Application.EnableEvents = False
        Call LoopTable
        Application.EnableEvents = True
End Sub
[\code]

Code: Select all

For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row

Code: Select all

Option Explicit

Private Sub Worksheet_Calculate()
Application.EnableEvents = True
    If Range("B20").Value > 0 Then
        Call LoopTable
      Application.EnableEvents = False
    End If
End Sub

Code: Select all

Public tbl As ListObject
Function GetTableData(projRow)

projDesc = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Desc").Index)
projStatus = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Status").Index)


projFullMsg = projDesc & " " & vbCrLf & _
"Çѹ·Õè : " & Now()


GetTableData = projFullMsg

End Function

Sub LoopTable()
Set tbl = Sheet1.ListObjects("ProjectTable")

'get value
Value = 0

'check number of rows
NumRows = tbl.DataBodyRange.Rows.Count

'Due Already
    CountDue = 0
    For i = 1 To NumRows
        projStatus = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Status").Index)
        If projStatus > Value Then
        DueMsg = DueMsg & GetTableData(i) & Chr(13) & Chr(10)
        CountDue = CountDue + 1
        End If
    Next i
    
    MsgBox "Warning : " & CountDue & " items" & Chr(13) & Chr(10) & DueMsg
    
    
End Sub