VBA Code for upcoming deadline pop-up - message to show info from 2 other columns plus # of days until deadline

[aliecat08]

I am a complete VBA novice and searched this forum to solve my issue but I still can't figure it out. I have a workbook with 3 worksheets and need a pop-up when opening the workbook. All the info for the pop-up will be derived from the 1st sheet which is named "OpenCases"


Here is a screenshot of my columns:

I want the pop-up to show the First and Last Name of each person whose "Statute" is 60 days or less away AND "Status" is Pre-lit. So the message will state, for example, "Jane Doe statute expiring in 20 days", "Hotel Doe statute is 59 days away."

PLEASE HELP!

 

[tj4242]

Do you really need the pop-up? Or is it enough just to flag the rows that meet the condition? Like this?

Book1
	A	B	C	D	E	F	G
1	Last name	First name	DOL	Statute	Status	Attorney(s)	Assistant
2	DOE	ALPHA	3/8/2021	3/8/2023	Pre-lit	Romeo	Whiskey
3	DOE	BRAVO	4/28/2021	4/28/2023	Pre-lit	Romeo	Whiskey
4	DOE	CHARLIE	9/30/2021	10/2/2023	Filed	Romeo	Whiskey
5	DOE	DELTA	9/13/2021	9/13/2023	Pre-lit	Romeo	Whiskey
6	DOE	ECHO	10/18/2021	10/18/2023	Filed	Romeo	Whiskey
7	DOE	FOXTROT	2/18/2021	2/20/2023	Pre-lit	Romeo	Whiskey
8	DOE	GOLF	12/1/2021	12/1/2023	Filed	Romeo	Whiskey
9	DOE	HOTEL	5/10/2021	5/10/2023	Pre-lit	Romeo	Whiskey
10	DOE	INDIA	5/19/2021	5/19/2023	Settled	Romeo	Whiskey
11	DOE	GOLF	12/1/2021	12/1/2023	Filed	Romeo	Whiskey
12	DOE	HOTEL	5/10/2021	5/10/2023	Pre-lit	Romeo	Whiskey
13	DOE	INDIA	5/19/2021	5/19/2023	Settled	Romeo	Whiskey
OpenCases

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:G13	Expression	=AND(($E3="Pre-lit"),(NETWORKDAYS(TODAY(),$D3,)<60))	text	NO
A2:G2	Expression	=AND(($E2="Pre-lit"),(NETWORKDAYS(TODAY(),$D2,)<60))	text	NO

The problem is if you have more than one that meets the Pre-lit and 60 days or less. Let's say you have 400 items that meet that condition. Do you want to have to 400 pop-ups that you have to hit OK? Or a single pop-up that has 400 lines on it.

Finally, what constitutes the 60 days? 60 calendar days? 60 business days? Does the 60 days include holidays? I went with straight business days using the =networkdays() function without the holidays parameter.

 

[aliecat08]

I actually need the pop-up, not the conditional formatting. We're never going to have 400 lines. The max cases with statutes 60 days (calendar not business) will be less than 5. So I just need one pop-up with lines for each case meeting the criteria. The pop-up will look like this:

UPCOMING STATUTES:

ALPHA DOE (DOL: 3/8/2021) statute expiring in 20 days

FOXTROT DOE (DOL: 2/18/2021) statute expiring in 14 days

 

[tj4242]

Go to ThisWorkbook and add this code.

Private Sub workbook_open()
    Dim LastRow As Long
    Dim srcrow As Long
    Dim popup_message As String
       
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    popup_message = "UPCOMING STATUTES:" & vbNewLine & vbNewLine
   
    With Worksheets("OpenCases")
    For srcrow = 2 To LastRow                           'skip the title row so start at row 3
        If .Range("E" & srcrow) = "Pre-lit" Then        'Pre-lit is the first criteria
            If .Range("D" & srcrow) - Date < 60 Then    'Statute date < 60 days is the second criteria
                'Build message
                popup_message = popup_message & .Range("B" & srcrow) & " " & .Range("A" & srcrow) & " (DOL: "
                popup_message = popup_message & .Range("D" & srcrow) & ") statute expiring in " & .Range("D" & srcrow) - Date & " days"
                popup_message = popup_message & vbNewLine & vbNewLine
            End If
        End If
    Next
    End With
   
    MsgBox popup_message                                'display the popup with the message
End Sub

 

[aliecat08]

I did but the pop up only shows "UPCOMING STATUTES:"

I copy pasted the exact code using the copy icon from your post. It looks like this:

Private Sub workbook_open()
    Dim LastRow As Long
    Dim srcrow As Long
    Dim popup_message As String
       
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    popup_message = "UPCOMING STATUTES:" & vbNewLine & vbNewLine
   
    With Worksheets("OpenCases")
    For srcrow = 2 To LastRow                           'skip the title row so start at row 3
        If .Range("E" & srcrow) = "Pre-lit" Then        'Pre-lit is the first criteria
            If .Range("D" & srcrow) - Date < 60 Then    'Statute date < 60 days is the second criteria
                'Build message
                popup_message = popup_message & .Range("B" & srcrow) & " " & .Range("A" & srcrow) & " (DOL: "
                popup_message = popup_message & .Range("D" & srcrow) & ") statute expiring in " & .Range("D" & srcrow) - Date & " days"
                popup_message = popup_message & vbNewLine & vbNewLine
            End If
        End If
    Next
    End With
   
    MsgBox popup_message                                'display the popup with the message
End Sub

 

[aliecat08]

OH WAIT NM!! The workbook I tested on has the Status column under "G" not "E". Once I tweaked the code to reflect this, it worked.

THANK YOU THANK YOU THANK YOU!!!

 

[tj4242]

The code is looking for the Status (Pre-lit, Filed, etc) in column E like your first example. The one you just posted has the Status in column G. That is why it is not working.

Change this

If .Range("E" & srcrow) = "Pre-lit" Then

to

If .Range("G" & srcrow) = "Pre-lit" Then

 

[tj4242]

We both noticed that at the same time

 

[aliecat08]

We both noticed that at the same time

Haha yup!

Can you PLEASEEE help me with another code for this workbook?

I want to move the entire row from the OpenCases sheet to ClosedCases sheet when "Status" column is changed to "Settled" or "Dropped" or "Closed". In the ClosedCases sheet, I want it autosorted by the "Last Name" column. I also want to do the reverse, in case we mistakenly move a case to ClosedCases sheet.

And ideally, a message that pops up before the move that confirms the move. Something like "Are you sure you want to move the case to Closed Cases?" and "Are you sure you want to move the case to Open Cases?"

When I first tried this 6 years ago, I got a code from some site but it's a complete mess and based on designated Names so the slight change causes a bunch of issues.

I'd really appreciate your help!!

 

[tj4242]

I think the best thing is to put this request into a separate post. That way if I cannot get to it, someone else might pick it up. If people see something is already answered, they may not want to read it.