Pop up message to say invoice to mature in 15 days

[HYKE]

Hi,

I have invoice due dates in column B, what I want to happen is for excel to collect for me information in column A of the invoices to mature in 15 days from todays date. I have found this code in one of excel sites, but don't know how to make it work the way I want, can somebody assists once more....

Thanks,

Dim Mycell
Private Sub Workbook_Open()
Dim Rng
Dim strText As String
Set Rng = Sheets("Sheet1").Range("B1:B25")
For Each Mycell In Rng
If Mycell.Value < Date Then
strText = strText & vbLf & Mycell.Offset(0, -1).Value & " - " & Date - Mycell.Value & " Days"
End If
Next Mycell
If Len(strText) > 0 Then
MsgBox strText & vbLf & "Are Overdue. Take Action Now! ", vbOKOnly Or vbExclamation, "Tasks Overdue"
End If
End Sub

 

[Marcelo Branco]

Hi,

The code you've posted does:

Runs when the file is opened

Compare dates in Range B1:B25 of Sheet1 with Date (today)

Warns the user, by a message, for all dates in B1:B25 that are less than today, showing the corresponding value in column A and the difference of dates in column B from today,

Is this ok for you?

M.

 

[HYKE]

Hi,

Thank you for looking into my querry...

What I want to happen is compare dates from B1:B25 and look for dates which will be maturing in 15 days from today's date, and take the corresponding information from column A. Say Row 2 of column A whose date in column is 18/07/11, I want the message box to pop up having these two information and so goes down the column if there still any invoice mature in 15 days. so to mention that the dates in B1:B25 is a result of conditional formatting '=AND($A2<>"",$A2<=$H$1+15) .

Thanks,

HYKE

 

[Marcelo Branco]

Hi,

If
a)you want the macro running everytime the file is opened (well... i dont like it - its boring. I would prefer to assign the macro to button, but its up to you)

b)the range is fixed, ie, B1:B25, and never changes and the dates are in Sheet1

Try this

Private Sub Workbook_Open()
    Dim Mycell As Range, rng As Range
    Dim strText As String
    
    Set rng = Sheets("Sheet1").Range("B1:B25")
    
    For Each Mycell In rng
        If Mycell.Value < Date + 15 Then
            strText = strText & vbLf & Mycell.Offset(0, -1).Value & _
                " - " & Date + 15 - Mycell.Value & " Days"
        End If
    Next Mycell
    
    If Len(strText) > 0 Then
        MsgBox strText & vbLf & "Are Overdue. Take Action Now! ", _
            vbOKOnly Or vbExclamation, "Tasks Overdue"
    End If
End Sub

To insert this macro:
Copy the code above
Alt+F11 to open VBEditor
Double-click in ThisWorkbook (left-panel under VBA Project)
Paste the code in the rigth panel over the Workbook_Open event automatically created.

HTH

M.

 

[Biz]

Hi,

Marcelo Branco has answered the question but if there are not that many invoices in month then it is better variable range.

Sub Reminder()
    Dim Mycell As Range, rng As Range
    Dim strText As String
    Dim LastRow As Long
    
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
    
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Sheets("Sheet1").Range("B1:B" & LastRow)
    
    For Each Mycell In rng
        If Mycell.Value < Date + 15 Then
            strText = strText & vbLf & Mycell.Offset(0, -1).Value & _
                " - " & Date + 15 - Mycell.Value & " Days"
        End If
    Next Mycell
    
    If Len(strText) > 0 Then
        MsgBox strText & vbLf & "Are Overdue. Take Action Now! ", _
            vbOKOnly Or vbExclamation, "Tasks Overdue"
    End If
    
    'Release memory
    Set rng = Nothing
    
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        .DisplayAlerts = True 'Turn OFF alerts
        .EnableEvents = True 'Prevent All Events
    End With
End Sub

Biz

 

[Marcelo Branco]

Hi,

Marcelo Branco has answered the question but if there are not that many invoices in month then it is better variable range.

Biz

You are absolutely right, but i asked: if the range was fixed and never changes; if it was no better assign the macro to a button or to a shortcut, etc...

I did all these caveats.

M.

 

[graphitemonotones]

Hi,

How do I check if each cell in column B is not empty and is a valid date before the pop-up prompts me which item in column A is overdue?

Thanks.