Message box for expired dates from multiple sheets

Hello everyone,

I'm currently doing my internship and my assignment is to create a dashboard for a company of all their certified assets. I have 7 sheets that have multiple tables with certificates and their expire dates. Below you will find an example of one of the many tables:

Instrument ID Range Serial number Calibration date Expire date Status
01-003-0006 50-63 1528 14-Aug-2019 14-Aug-2020 Expired
01-003-0009 200-500 681884 9-May-2019 9-May-2020 Expired
01-003-0012 50-63 17104 30-Jul-2021 30-Jul-2022 Certified
01-003-0015 125-150 149664 16-Jul-2020 16-Jul-2021 Expired
01-003-0018 100-125 150019 16-Jul-2020 16-Jul-2021 Expired
01-003-0028 100-2100 26234146 19-Jan-2021 19-Jan-2022 About to expire
01-003-0029 50-63 30762 24-May-2019 24-May-2020 Expired

The 'Status' column automatically changes to 'About to expire' when the date is 30 days away from expiring.

Now, I want a pop-up messagebox every time the excel workbook is opened saying which certificate is about to expire and which expired already. See attached picture: msgbox excel example.PNG

Is there a code in VBA that I can apply for all tables on every sheet? Please let me know.

Kind regards,
Caleb

I would use Conditional Formatting instead of displaying a MsgBox ...It is easier to set up plus visually easier to spot the cells involved ... Just imagine how many MsgBoxes you would end up with each time you open the workbook if there happens to be a lot of cells showing "expired" or "about to expire" . It would quickly get out of control.

I completely agree with you. I already did conditional formatting. The status column changes highlight color depending on the status. But my internship supervisor insists on the message box. That's why I can not just forget about the idea.

Could you give me an example?

Thank you for your reply. Can you explain me why I need two sheets named "Expired" and "About to expire"? Do I need to place any information in these sheets?

Okay I understand now. Can you tell me which values I would have to change in the code if my 'Status' column starts at cell G15?

Okay give me a few minutes and I will attach it.

Here is a de-sensitized copy of the dashboard. Let me know if there is anything else I need to provide.

Yes I know. Most sheets have multiple tables. Most of the first tables do have the status column in the 'G' column. If not, is there a way where I can just copy paste a code for every table and just change some values according to the table?

Oh my bad I did not see that question. Yes that is correct. I want to link it with their instrument ID. The tables that do not have an instrument ID are the welding machines of the sheet 'Pipefitters' and 'Steel shop', Magnet from the sheet 'Steel shop' and transport. Let the serial number of the welding machines act as their instrument ID (in the file I sent you they have the same serial numbers but in the actual dashboard all serial numbers are unique). The crane # can act as the 'instrument ID' in the table of cranes in the 'Transport' sheet. Don't worry with the table of crane/forklift operators for now.

Let me know if I missed anything.

Yes sorry I posted this question on another forum but did not get any feedback. I appreciate the feedback I am receiving on this forum. I tried uploading the link but I am getting the following error: "You are not allowed to post any kinds of links, images or videos until you post a few times." How should I proceed?

Yes sorry I posted this question on another forum but did not get any feedback.

You actually posted on the other forum AFTER you posted here & AFTER you had already received responses here.

Yes correct because they do not have serial numbers, or the company does not know the serial numbers. For that reason I don't think we can use serial number instead of instrument ID.
The headers is a type error from me, all should just be "Serial number".

I am not familiar with how macro's work in Excel, but is it possible to create a different macro for these tables? For example the welding machine tables we use the serial number as the instrument ID? And the crane table we use crane #?

Let me know if this is possible.

Okay I understand. Can you leave those out then and only write a macro for the tables with the instrument ID in them?

I am not sure how I would have to modify the other tables so that they all have the same format.

We can insert a column, but some assets just don't have an instrument ID. So the column would stay blank. Or I would have to give them a 'fake' instrument ID.

Okay I did some changes. Let me know if they're good.

Thank you! I appreciate the time you're putting in.

Thank you for your reply. However, it is not working by me. I am receiving an error with 'macro 1':

"Run-time error '1004':
Autofilter method of Range class failed" See attached picture for the error".

With the second macro, I do not see the report. See the other attached picture to see what I mean.
Let me know if it's something that I am doing wrong or that I need to enable.

Okay just to be clear, after pressing ALT + F11, delete the 'Sub Macro1() and Macro 2()"?

Nevermind, I deleted the macro's and now it's working. Thank you very much! All though, I still have the problem where my Home sheet changes and deletes some shapes. See attached picture. Would you know what is the cause of this?

Yes I am running a code that I found online so that whenever the Workbook is opened, it will always open on the Home sheet:
Private Sub Workbook_Open()
Dim xOWSName As String
Dim xWS As Worksheet
xOWSName = "Home"
Set xWS = Worksheets(xOWSName)
xWS.Activate
End Sub

I can not find it in the code however but I remember putting it in. Would this code affect it?

Okay I think after deleting the first two macros, I solved the issue. Once again, thank you very much for your time. I highly appreciate it!