How to make msgbox display total found

[malaya tigre]

Hi..

I got this formula in B column which calculating value on F column...
=IF(F2<=TODAY(), "EXPIRED", IF(AND(F2-TODAY()>0, F2-TODAY()>=10), "ACTIVE", "REMINDER"))

By using VBA code, how can i have a message box that tell me such like "62 REMINDER found to update"?

Thanks..

 

[Ruddles]

This will do the job:-

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim iReminders As Long
  
  iReminders = WorksheetFunction.CountIf(Columns("B"), "REMINDER")
  
  MsgBox Format(iReminders, "#,##0") & " REMINDER found to update", vbInformation + vbOKOnly

End Sub

Put this in the code module for the worksheet your data is in. It will fire every time anything in your worksheet changes. Get that working first and then decide whether that's actually too often and if so, we can look at it again.

 

[Martvg]

Hi,

Hope this helps:

Sub Expired_Left()

    MsgBox WorksheetFunction.CountIf(Range("B2:B" & Range("B10000").End(xlUp).Row), "REMINDER") & " REMINDERS found to update"
    
End Sub

Best regards,
Mart

 

[mole999]

how you might trigger it I haven't shown

Sub test()
MsgBox "62 REMINDER found to update", vbOKOnly, Application.Name
End Sub