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.

 

[Josh381991]

thank you very much! Works a treat!

 

[Relearning]

This message is for Anthony47.
I have spread sheet with several trucks that regularly we need to be update casue of mechincal sercives done to Tractors. I will attached the excel sheet to see if you could help me to either create an automaticly macro or or script that will notify me when the next vehicles are due for their service. Maybe opening another excel page or highlightinh and moving to the top of the page when are getting closer to 60 days from their last date I recorded their service. let me know if there is something you think could help me to accomplish this automated sheet .

 

[Anthony47]

You were wrong to queue you request on this old thread and address it to me: you lost the opportunity to receive support from thousands of experts...
Anyway...

You can easily obtain a list with the coming deadline using standard functions of XL2021

For example:
1) Add a new sheet to your workbook and name it "Coming" (or the name you prefer)
2) In A2 write 60, or any paeriod of days notice that you prefer
3) In B2 insert this formula:

=LET(EarlyWarn,A2,TruckList,'NameOfTheSheet'!A3:E1000,NextDeadline,'NameOfTheSheet'!B3:B1000,FILTER(TruckList,(TODAY()-NextDeadline)<EarlyWarn))

You need to customize the "fields" according your situation. Namely
-TruckList, should be followed by the coordinates of the table that list the trucks and their details
-NextDeadline, should be followed by the range (within the TruckList) that contains the deadline to be checked

This formula should return the list of the trucks whose deadline is within the threshold written in A2

Adapt to your need; if you don't succeed then specify which is the layout of your truck list and which column is the one with the deadline.

 

[Relearning]

Thank you for your help. I did repost it in a new thread after i did post directing to you. always learning

 

[Relearning]

I hope you can understand my knowledge is very limited and trying to make it happen will might not be ease but i will do my best to explain. Spread sheet name is 60 Dasy Tractor PMS. each columns is very similar but the one columns i really care about is D, lebael 60 Days. I have a list that cover three pages but if i can do this for few rows and columns i think i should be able to replicate it. what i just need is to use the warning option to warn me truck XX is almost due for service or close to 60 days in a secon page that pops on my screen each monirng so i can handle it first thing and schedule for outside company to pickup trucks and do service. Maybe highlight with a color and only those vehicles close to the 60 days

60 Days Schedule
TRUCK	Current Date	40 days	60 Day	ANNUAL
1000
1001SD	09/03/21	10/13/21	11/02/21	03/02/21
1002SD	11/15/22	12/25/22	01/14/23
1003AZ	08/24/22	10/03/22	10/23/22	09/24/21
1004	01/07/23	02/16/23	03/08/23	01/07/23
1005 SD	12/28/22	02/06/23	02/26/23	04/30/21
1006	01/07/23	02/16/23	03/08/23	01/07/23

 

[Fluff]

@Relearning for future reference
Please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.