VBA help - Why am I getting multiple pop-up boxes instead of just 1 when opening workbook??

[aliecat08]

So I had previously posted asking for a code for a pop-up showing upcoming deadlines, but didn't get responses so I tried to come up with my own code. But now, I'm getting multiple pop-ups instead of just one.

I want 1 pop-up that shows the First, Last Name and DOL for each person whose "Statute" is 60 days or less away AND "Status" is Pre-lit. So the message will look like this:

UPCOMING STATUTES:
Jane Doe statute expiring in 20 days
Hotel Doe statute expiring in 59 days

Here is my code:

Private Sub Workbook_Open()
    Dim bottomG As Integer
    bottomD = Range("D" & Rows.Count).End(xlUp).Row
    Dim c As Range
    For Each c In Range("D2:D4000")
        If c >= Date And c <= Date + 60 Then
            MsgBox c.Offset(0, -2) & " " & c.Offset(0, -3) & " " & "DOL: " & c.Offset(0, -1) & " statute expiring in " & c - Date & " days."
        End If
    Next c
End Sub

Here is my table:

Please help!

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Try this code instead

Private Sub Workbook_Open()
  Dim bottomD As Long
  Dim t As String
  
  bottomD = Range("D" & Rows.Count).End(xlUp).Row
  t = "UPCOMONG STATUTES:"
  Dim c As Range
  For Each c In Sheets("OpenCases").Range("D2:D" & bottomD)
    If c >= Date And c <= Date + 60 Then
      t = t & vbLf & c.Offset(0, -2) & " " & c.Offset(0, -3) & " " & "DOL: " & c.Offset(0, -1) & " statute expiring in " & c - Date & " days."
    End If
  Next c
  MsgBox t
End Sub

 

[aliecat08]

Thank you!!!

Is there a way to change the font and justification for "UPCOMING STATUTES:". I'd like it to be red and in the center of the message box.

 

[Peter_SSs]

Thank you!!!

You're welcome.

Is there a way to change the font and justification for "UPCOMING STATUTES:". I'd like it to be red and in the center of the message box.

No. At least not without a lot of effort (look here for example at the complexity of altering features of a standard MsgBox)
You could instead consider designing a pop-up UserForm instead, though that is not the sort of question I usually get involved in.