Displaying a range in a pop up box - with each result on a new line

[BoffleHoffer]

I have a table that monitors due dates, with several different due dates on each row.

On opening the workbook, I've got some VBA code that scans a range of cells to see if an item is within 7 days of "due", and opens a pop up box listing the name of those items.

When the current date is within 7 days of any of the due dates on that row, I use the following code to list the item names in the popup box using an offset to return results from a specific column where the item names are listed.

If Date >= DateDue - Range("B2") Then

    'Change the offset value to pick up the Item Name column in your data

        NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -DateDue.Column + 1)

Problem is that it displays all resulting item names on one line in the pop up box, and I would like to make it display each result on a new line. How would I do that?

Currently my pop up box looks like:
"The following items are about to become due:"
"Item 1, Item 2, Item 3, Item 4," etc

Whereas I would like it to read:
"The following items are about to become due:"
"Item 1,
Item 2,
Item 3,
Item 4,"
etc As I think it would look tidier like this

This is the full code I am using at the moment:

Option Explicit

Private Sub Workbook_Open() 
'makes the code run every time the workbook is opened

Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String
Set DateDueCol = Range("G8:G12, K8:K12, O8:O12, S8:S12 ") 'the range of cells that contain your due dates

For Each DateDue In DateDueCol

    'B2 is the cell that tells the code to display popup box when due date is less than 7 days away.
    If Date >= DateDue - Range("B2") Then
    'Change the offset value to pick up the Item Name column in your data
        NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -DateDue.Column + 1) 'This ensures that no matter which column the relevant due date is in, only results from the first cell in that row are displayed in the pop up box.

    End If

Next DateDue

If NotificationMsg = "" Then

    MsgBox "No items are due."

Else: MsgBox "The following are about to become due: " & vbNewLine & vbNewLine & NotificationMsg

End If


End Sub

Cheers

 

[mse330]

Try to change below ...

'change below line
 NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -DateDue.Column + 1)

'to this
NotificationMsg = IIf(NotificationMsg = "", DateDue.Offset(0, -DateDue.Column + 1), NotificationMsg & vbLf & DateDue.Offset(0, -DateDue.Column + 1))

 

[BoffleHoffer]

NotificationMsg = IIf(NotificationMsg = "", DateDue.Offset(0, -DateDue.Column + 1), NotificationMsg & vbLf & DateDue.Offset(0, -DateDue.Column + 1))

Ah mate that's perfect, thank you so much!

The table basically keeps an eye on staff training that needs renewing now and again.
There's several due dates on each row as each staff member has several bits of training they'll need to renew from time to time.
Each Due Date on that row relates to a different training module.

With your edit plus a bit of extra code, I now have the offset configured to display the name of every staff member *and* the training module that is about to expire.
If there are multiple training modules about to expire for the same staff member, that person's name is duplicated on a new line for every due date on their row with the relevant module name next to it. For example:

The following items are about to become due:
Bill - Module 1
Bill - Module 4
Alan - Module 3
Pete - Module 1
Steve - Module 1
Steve - Module 3

This is the modified code I've used to achieve this.

NotificationMsg = IIf(NotificationMsg = "", DateDue.Offset(0, -DateDue.Column + 1) & ":  " & DateDue.Offset(-DateDue.Row + 6, -2), NotificationMsg & vbLf & vbLf & DateDue.Offset(0, -DateDue.Column + 1) & ":  " & DateDue.Offset(-DateDue.Row + 6, -2))

Hope someone else gets some use out of this too.
Thanks again!

 

[BoffleHoffer]

Frustratingly though, when I open the file the message box appears but the message box title is now missing and all the nice formatting that the code was previously doing is no longer there.

It's all just a mass of info on one line. If I open the editor and run it again though, title and formatting are back.

I've tried using different codes to display a title in the message box as well as removing any Title code for the message box altogether, but when I reopen the file again the same issue occurs, however it's ok if I run it in the editor so I'm not sure what I'm doing wrong here. Here is the code in its entirety:

Private Sub Workbook_Open()

Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String

Set DateDueCol = Range("F8:F12, I8:I12, L8:L12, O8:O12") 'the range of cells that contain your due dates

For Each DateDue In DateDueCol

    'Change B2 to the cell for bring forward reminder days in your data
    If Date >= DateDue - Range("B2") Then
    'Change the offset value to pick up the invoice number column in your data
        NotificationMsg = IIf(NotificationMsg = "", DateDue.Offset(0, -DateDue.Column + 1) & ":  " & DateDue.Offset(-DateDue.Row + 6, -2), NotificationMsg & vbLf & vbLf & DateDue.Offset(0, -DateDue.Column + 1) & ":  " & DateDue.Offset(-DateDue.Row + 6, -2))
        
    End If

Next DateDue

If NotificationMsg = "" Then

    MsgBox "Records indicate all sub-contractor cover is currently valid.", Title:="Cover OK!"

    
Else: MsgBox "The followinging sub-contractors' cover is missing or about to expire: " & vbNewLine & vbNewLine & NotificationMsg, Title:="Expiry Warning!"

End If


End Sub