Msbbox Notice (4 days)

When do you want the notice displayed?

Hi Macroshadow,

I should have mentioned that requirement.

On file open
I am not sure how many options there are actually.

So for the screen shot above, you would have received a popup message on 10 Feb telling you that there are amounts outstanding on 14 Feb??

If these were actually the 15 Feb, the warning would have been needed on the 11 Feb. As that was a Saturday would the file have been opened on that day? If the file didn't get opened until Monday 13 Feb, we have missed the 4 day warning window; what do you need to happen in this situation?

This may be overkill, but give it a try. It assumes that you want to know about amounts that are due in 4 or less days. If that's not the case then it can be changed.

Note that it could be changed so that it's sorted by days and there could be various colors for different amount of days.
29002403.xlsm

Hi guys,  sorry I don't respond earlier but I am nine hours ahead ofof the usa.  

 Rob, to answer your question and possibly Martin's as well if the notification is on the weekend then it would need to be during the weekday but actually not on Friday because Friday and Saturday are the are the weekends in Saudi Arabia

 Martin, I will look at the Excel file shortly. Thank you very much

Hi Martin, it looks like I have a missing reference:  MS FlexGrid Control 6.0 (SP6).  I am not sure how I can install or if there is a workaround?

Right-click on the Toolbox and select Additional Controls. Is "Microsoft Flexgrid Control..." there?

Here is a version that uses a listview control.
29002403a.xlsm

Hi Martin,

I checked the additional controls and it doesn't look like I have Microsoft Flexgrid Control.  
I tried the most recent file (a) but it still gives me a missing reference to the Microsoft Flexgrid Control again.

here is a screen print showing I don't have Microsoft Flexgrid Control

let me know what is next.  thank you

Un-check the "MISSING Microsoft..." control and try the second one again.

Martin, I unchecked the "Missing" Microsoft..." but I get an error of "could not load object because it not available on machine".   I also get a "variable not defined" and it highlights Listview1 in the line Set clnx = Listview1

Nice!  
I have a follow up.  I am transferring this form and code to the official excel file.  I have done this but the amounts in this new file are in row 33 whereas in the sample file they were in row 2.  Could you be so kind to let me know where I would make this adjustment?  I attempted to do this but I don't think it worked.

thank you so much!

attached is a screen print of the file I transferred the form and code to.  Notice its row 33 where the data is.  

The attached uses code that fits your data as shown in the picture. In that code I've added to constants that reflect the rows numbers of the dates and the Daily Draw amounts. Let me know if it works for you.

Would you like the results sorted, and/or have those due in 4 days say "Due" and those due in less than 4 days say "Overdue"?
29002403c.xlsm

That new "tickler" might look like this, where "Overdue" amounts are due in less than 4 days, "Due" are due in 4 days and the rest are due in more than 4 days.

Hi Martin,

Thank you so much.  

Coould I request that the msgbox have only the amount, days, and the date?  Sorted by date would be nice to have. Also, i would not need the overdue to be displayed.

Thank you Martin. You have helped me more than expected.

Martin, if I have many sheets, would the sheet name need to be referenced?  If so, the sheet name is "Forecast".   Not at a computer yet but will be soon.  

Thank you

I added line 2 in the following code so that the workbook will always open to the Forecast sheet.

Private Sub Workbook_Open()
Sheets("Forecast").Activate
UserForm1.Show

End Sub

Select allOpen in new window

And in the code for the UserForm I changed line 9 so it no longer refers to Sheet1.

Private Sub UserForm_Initialize()
    Dim intRow As Integer
    Dim lngCol As Long
    Dim ws As Worksheet
    Const SPACES = "     "
    Const DATE_ROW = 1
    Const AMOUNT_ROW = 33
    
    Set ws = ActiveSheet
    
    With ListBox1
        For lngCol = 2 To ws.UsedRange.Columns.Count
            If ws.Cells(AMOUNT_ROW, lngCol) > "0" Then
                .AddItem
                .List(.ListCount - 1, 0) = FormatCurrency(ws.Cells(AMOUNT_ROW, lngCol))
                .List(.ListCount - 1, 1) = SPACES & DateDiff("d", Now, ws.Cells(DATE_ROW, lngCol))
                If DateDiff("d", Now, ws.Cells(DATE_ROW, lngCol)) > 0 Then
                    .List(.ListCount - 1, 1) = " " & .List(.ListCount - 1, 1)
                End If
            End If
        Next
    End With
End Sub

Select allOpen in new window

29002403d.xlsm

Hello Martin,

I have moved over the userform and associated VBA to my official excel file.
I like the look.  Very nice.

I have many columns and with dates along row 1 and many in the past.  
As of right now, the msgbox is displaying those past due amounts.

I kindly request to only show the msgbox if any amounts are due within 4 days and if no amounts are due then do not display the msgbox.  
I also need to have the date to show

thank you

in the screen print above, if the msgbox would display the $100 and $200 that would be perfect.  thank you in advance Martin.

Could I request that the msgbox have only the amount, days, and the date?  Sorted by date would be nice to have.

I missed that and so let me make sure that I know what you want. Note that I'm sure of some of the following and I just want to put all the requirements in one place.
1. Do you want to display amount, days and date, or just amount and days?
2. Please verify that if the date is 2/18 that you only want to see amounts from 2/18, 2/19, 2/20, 2/21 and 2/22.
3. Please verify that if there are no amounts from those 5 days that you don't want the tickler to appear.
4. Should the amount from 2/18 say "Due" or should it say "0"?
5. Do you want it sorted by Due in Days?
6. Are the amounts always three digits? In other words always between 100 and 999 inclusive?
7. Do the amounts ever have cents?
8. Anything else?

Hi Martin.

I will copy and paste the questions

Could I request that the msgbox have only the amount, days, and the date?  Sorted by date would be nice to have.
I missed that and so let me make sure that I know what you want. Note that I'm sure of some of the following and I just want to put all the requirements in one place.
1. Do you want to display amount, days and date, or just amount and days?
      Answer: Amount; Days; Date

2. Please verify that if the date is 2/18 that you only want to see amounts from 2/18, 2/19, 2/20, 2/21 and 2/22.
       Answer: correct (but only if there is an amt.  if 0 then do not display

3. Please verify that if there are no amounts from those 5 days that you don't want the tickler to appear.
       Answer: correct

4. Should the amount from 2/18 say "Due" or should it say "0"?
      Answer: if there is a 0, do not display.  (Just thought of this)

5. Do you want it sorted by Due in Days?
      Answer: pls sort by date ascending

6. Are the amounts always three digits? In other words always between 100 and 999 inclusive?
      Answer: 2 - 4 digits

7. Do the amounts ever have cents?
      Answer: no cents

That does cover it.

Thank you sir...

4. Should the amount from 2/18 say "Due" or should it say "0"?
      Answer: if there is a 0, do not display.  (Just thought of this)

Are you saying that if an amount is due today that you don't want to know about it?

In any case would you be willing to ask for these changes in a new question?

Hi Martin, actually if the amount is zero then I would not want it displayed. Maybe I was mixing up the due days and the amount. If the due days says zero then yes I would want that displayed.

Yes,  I will ask another question and refer to this question. I guess that's how you do it. Thank you for suggesting this. I should have already done that.

Martin, i have opened another question.  Please follow below link.
Thank you Martin.  Truly appreciate your kindness.  

https://www.experts-exchange.com/questions/29003542/Msgbox-tickler.html

Martin, just to clear up the due days 0: if the due days is 0 & amount is 0 then not necessary to display.

I think I understand but as I see it there are two separate rules which are:

1) If the amount is zero the item is never displayed
2) Otherwise  the amount is displayed if it is due any day from today to 4 days from today. (A span of 5 days).