Excel VBA pop up message help

[Josh381991]

Good afternoon,

I am super new to VBA and hoping for some help.

I have a training log for all employees which lists modules and dates of expiry. I am attempting to get a pop up that automatically tells me what employees have outstanding training due. I used and amended a code from the internet which I have posted below. This works to a degree in that it pulls up what is expiring but it pulls up the dates rather than the employees name as shown below.

The employees names are in column A5 to A37 with the modules being in Row 4 to column AO. The dates are within A5 to AO37.

The H1 bit in the code allows me to set number of days I wish to search ie all dates occurring within the next X days.

Ideally I would like the pop up to come up saying training due for the following employees. Employee A Module C Employee D Module X etc.

I really hope that makes sense.

Sub Due_Date()
Dim DueDate_Col As Range
Dim Due As Range
Dim PopUp_Notification As String
Set DueDate_Col = Range("C5:AO37")
For Each Due In DueDate_Col
If Due <> "" And Date >= Due - Range("H1") Then
PopUp_Notification = PopUp_Notification & " " & Due.Offset(0, -2)
End If
Next Due
If PopUp_Notification = "" Then
MsgBox "No outstanding training"
Else: MsgBox "Training due for these individuals: " & PopUp_Notification
End If
End Sub


Thank you in advance.

 

[Anthony47]

Try modifying the PopUp_Notification line to

PopUp_Notification = PopUp_Notification & vbCrLf & Cells(Due.row, "A") & ": " & Due.Offset(0, -2)

This will output the Name and the date.

If it is useful, you might get the address of the cell containing the expiring date by replacing Due.Offset(0, -2) with Due.Address(0, 0)

 

[Josh381991]

Try modifying the PopUp_Notification line to

PopUp_Notification = PopUp_Notification & vbCrLf & Cells(Due.row, "A") & ": " & Due.Offset(0, -2)

This will output the Name and the date.

If it is useful, you might get the address of the cell containing the expiring date by replacing Due.Offset(0, -2) with Due.Address(0, 0)

Thanks Anthony,

This is now displaying the below;

Is it possible for me to add data from columns to this? In column 4 I have the different training modules listed from C4 to AK4.

Is it possible to get the pop up to say Employee C, Module B, 20/07/2023?

Thanks again for your help.

 

[Anthony47]

Is it possible to get the pop up to say Employee C, Module B, 20/07/2023?

"Yes", but we don't know which is the layout of your information so cannot say which is the coding; a snapshot taken using XL2BB addin would gratly help, or at least a picture of the relevant data.

Btw, the msgbox will last until you press ok, and in the meantime the workbook is locked; wouldn't be better putting this information on a separate worksheet?

 

[Josh381991]

"Yes", but we don't know which is the layout of your information so cannot say which is the coding; a snapshot taken using XL2BB addin would gratly help, or at least a picture of the relevant data.

Btw, the msgbox will last until you press ok, and in the meantime the workbook is locked; wouldn't be better putting this information on a separate worksheet?

Hi Anthony,

This is an example of the data on the spreadsheet:

Essentially I'm looking for the pop up the say Employee A Module A 11/04/2023

Thank you again

 

[Anthony47]

Try:

PopUp_Notification = PopUp_Notification & vbCrLf & Cells(Due.Row, "A") & " - " & Cells(1, Due.Column) & " - " & Due.Value

 

[Josh381991]

Try:

PopUp_Notification = PopUp_Notification & vbCrLf & Cells(Due.Row, "A") & " - " & Cells(1, Due.Column) & " - " & Due.Value

perfect! Thank you so much for your help!

 

[Anthony47]

Thank you for the feedback

 

[Josh381991]

Hello all,

Sorry to open this up again but in need of a little further help.

I have everything working as should be however the data set pulled no longer fits into the text box. Is there a way to get the alert box to write the information into word/notepad so i can view all of the data?

 

[Anthony47]

Replace this portion:

If PopUp_Notification = "" Then
MsgBox "No outstanding training"
Else: MsgBox "Training due for these individuals: " & PopUp_Notification
End If

with

If PopUp_Notification = "" Then
    MsgBox "No outstanding training"
Else
   Dim WordApp As Object
   Dim WDoc As Object
'
    'Create Word.Application
    On Error Resume Next
        Set WordApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        Set WordApp = CreateObject("Word.Application")
        Err.Clear
    End If
    On Error GoTo 0
    WordApp.Visible = True
    Set WDoc = WordApp.Documents.Add
    WDoc.Range(0, 0).Text = PopUp_Notification
    WordApp.Activate
End If

This should create and show a word document