Msgbox tickler

Give this a whirl.
29003542.xlsm

Martin, thank you.  

I copied over the UserForm and associated code to my excel file and the on workbook open codes as well.  
I am not sure if there is an issue with doing this.

I get a run time error 1004: sort order error.  

I don't get this error in your file uploaded though...only the file I am using.  

I will upload the file I am using in its entirety.
I imagine I am doing something wrong but the rows (dates and amounts) are in the same rows between both files.

when you have a sec, please take a peek at my excel file (it has the userform and codes taken from your file)
error in my file:

Edit by MartinLiss: Sensitive workbook deleted.

Martin.  Thank you so much for hanging in with me.  I am about to go to bed.  I will check the doc in about 11 hours, which is when i will be at a computer to test.

Thanks again

Oh and would appreciate if delete after i respond in the am (my am).

Since I, and I assume you, have a copy of the code, is there any reason why I can't delete them now?

Martin,

I wasnt able to download the file.  I am responding from my phone as computer is at work.  

Possibly you could send to my email:  
<email address removed>
Or repost is ok too.  
Thank you Martin.  Oh and btw, it has been a real pleasure working with you.  You obviously have a lot of experience.

I emailed it to you.

thank you Martin.  Sincerely appreciate your help and time spent.  It works perfectly!

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016

Yes sir... you were and then some.   Will take a look at the articles.  I think they will be interesting.

Hi Martin,

not sure if yo uare still monitoring.
Sorry to pester you.

I have a minor mod to request and I tried to do this myself but I don't understand where I am wrong.

I need to display amounts that are negative in addition to the positive amounts.  

I modified the below to <>"0" but that didn't fix it.  Not sure where else to modify.  
' Put the amounts due data on the temporary sheet
    For lngCol = 2 To ws.UsedRange.Columns.Count
        If ws.Cells(AMOUNT_ROW, lngCol) > "0" Then

thank you Martin.  




here is the entire code for the user form which is where I think the mod would be made:

Private Sub UserForm_Initialize()
    Dim intRow As Integer
    Dim intNewRow As Integer
    Dim lngCol As Long
    Dim intDays As Integer
    Dim ws As Worksheet
    Dim wsSort As Worksheet
    Dim strAmount As String
    Const DATE_ROW = 1
    Const AMOUNT_ROW = 33
    
    ' Postion controls
    ListBox1.Left = (Me.Width - ListBox1.Width) / 2
    ListBox1.Height = 115
    ' At left
    lblAmount.Left = ListBox1.Left
    ' Centered
    lblDue.Left = ListBox1.Left + (ListBox1.Width - lblDue.Width) / 2
    ' At right
    lblDueDate.Left = ListBox1.Width + ListBox1.Left - lblDueDate.Width - 4
    
    Set ws = ActiveSheet
    
    Application.ScreenUpdating = False
    
    ' create a new sheet
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sort").Delete
    On Error GoTo 0
    Set wsSort = ThisWorkbook.Worksheets.Add
    ActiveSheet.Name = "Sort"
    Application.DisplayAlerts = True
    
    ' Put the amounts due data on the temporary sheet
    For lngCol = 2 To ws.UsedRange.Columns.Count
        If ws.Cells(AMOUNT_ROW, lngCol) > "0" Then
            intDays = DateDiff("d", Now, ws.Cells(DATE_ROW, lngCol))
            If intDays >= 0 And intDays < 5 Then
                intNewRow = intNewRow + 1
                wsSort.Cells(intNewRow, "A") = ws.Cells(AMOUNT_ROW, lngCol)
                wsSort.Cells(intNewRow, "B") = intDays
                wsSort.Cells(intNewRow, "C") = CDate(ws.Cells(DATE_ROW, lngCol))
            End If
        End If
    Next
    
    ' Check to see if there any data to report
    If wsSort.Range("A1") <> Empty Then
        ' Sort the data on the temporary sheet
        With wsSort
            .Columns("B:B").Select
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("B:B"), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange wsSort.UsedRange
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    
            ' Put the sorted data in the listbox
            ' Column "A" is the amount and column "B" is the due in days
            If .UsedRange.Rows.Count > 8 Then
                ' Widen the listbox so that there's no need for a horizontal scrollbar
                ListBox1.Width = ListBox1.Width + 12
            End If
            For intRow = 1 To .UsedRange.Rows.Count
                ListBox1.AddItem
                strAmount = Format(.Cells(intRow, "A"), "$###,##0")
                strAmount = Right(Space(6) & strAmount, 6)
                ListBox1.List(ListBox1.ListCount - 1, 0) = strAmount
                ListBox1.List(ListBox1.ListCount - 1, 1) = Space(3) & .Cells(intRow, "B")
                ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(intRow, "C")
            Next
        End With
    End If

    ' Delete the temporary sheet
    Application.DisplayAlerts = False
    wsSort.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

You just need to change line 37. As written it was a way to ignore cells in row 33 that were blank, but the < "0" part also ignored negative values. Change the line to the following and you should be OK. It says to only ignore blank cells.

If ws.Cells(AMOUNT_ROW, lngCol) <> Empty Then

Select allOpen in new window

Thank you for your kindness, Martin.  Will be able to check at work and revert back...

works perfectly.  thank you!  have a good one...