SuperClock
New Member
- Joined
- Jul 31, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi All,
I've been searching through this forum and others for a few days, and with my still limited VBA knowledge, I've been unable to find a VBA script that can assist me in achieving my goal - most of the solutions are for preset future expiry dates.
To the question - I have a master spreadsheet that collates all the files our Administrative and Work Health & Safety teams are required to keep track of on a 1month/3month/6month basis. I have it set up so that the sheet pulls the files out of our shared folder into Column C (currently 7-50), and then updates Column E with the dates that each file was last updated. This has worked quite well, as staff only go into each of those files when they need to, so the last updated date is quite accurate.
However, there are times when staff go on leave, take on secondments, or the longer (6month + files) aren't updated due to staffing changes/movements etc. What I am hoping to achieve is as follows:
Example: File XXXX (Column C), was last modified on June 1st 2023 (Column E). This particular file should be updated on a monthly Basis, therefore if it falls out of that 30 day period, it needs to be seen to ASAP. Each time the Master sheet is opened, I would like the Solution that follows to apply:
Solution: A Pop Up Message Box, that reads all the Dates (Column E 7-50), and will release a series of Pop ups when the spreadsheet is opened (there will never be more than 2-3 Pop ups), that need to be acknowledged by pressing an OK button. The pop up box needs a message saying "File XXXX (Column C 7-50) is out of date". This will ensure each team is made aware of files they need to update to ensure our audits do not fail. It would be handy to have the pop up relay information such as "XXXX will be out of date in (x amount of) days", which could count down from 10 days prior to the required date. This could mean - 10 days before files reach 1 month prior to Today's date (File was last updated 10th of July, Today's date being 30th July, Pop up message File will be out of date in 10 days, and a message each day counting down until out of date, and then even if possible, continuing to alert as out of date beyond that month period). My sheet name to make it simple is '2023', and it is the only sheet that requires this pop up.
If anyone could please help by providing a code base for this that would be much appreciated! I only require the assistance for files that are 1 month past today's date, as I would like to try and manipulate the data for 3/6 month files and beyond to test my knowledge. I hope I have been concise and provided enough detail. Thank you in advance! Your expertise on this forum is invaluable.
I've been searching through this forum and others for a few days, and with my still limited VBA knowledge, I've been unable to find a VBA script that can assist me in achieving my goal - most of the solutions are for preset future expiry dates.
To the question - I have a master spreadsheet that collates all the files our Administrative and Work Health & Safety teams are required to keep track of on a 1month/3month/6month basis. I have it set up so that the sheet pulls the files out of our shared folder into Column C (currently 7-50), and then updates Column E with the dates that each file was last updated. This has worked quite well, as staff only go into each of those files when they need to, so the last updated date is quite accurate.
However, there are times when staff go on leave, take on secondments, or the longer (6month + files) aren't updated due to staffing changes/movements etc. What I am hoping to achieve is as follows:
Example: File XXXX (Column C), was last modified on June 1st 2023 (Column E). This particular file should be updated on a monthly Basis, therefore if it falls out of that 30 day period, it needs to be seen to ASAP. Each time the Master sheet is opened, I would like the Solution that follows to apply:
Solution: A Pop Up Message Box, that reads all the Dates (Column E 7-50), and will release a series of Pop ups when the spreadsheet is opened (there will never be more than 2-3 Pop ups), that need to be acknowledged by pressing an OK button. The pop up box needs a message saying "File XXXX (Column C 7-50) is out of date". This will ensure each team is made aware of files they need to update to ensure our audits do not fail. It would be handy to have the pop up relay information such as "XXXX will be out of date in (x amount of) days", which could count down from 10 days prior to the required date. This could mean - 10 days before files reach 1 month prior to Today's date (File was last updated 10th of July, Today's date being 30th July, Pop up message File will be out of date in 10 days, and a message each day counting down until out of date, and then even if possible, continuing to alert as out of date beyond that month period). My sheet name to make it simple is '2023', and it is the only sheet that requires this pop up.
If anyone could please help by providing a code base for this that would be much appreciated! I only require the assistance for files that are 1 month past today's date, as I would like to try and manipulate the data for 3/6 month files and beyond to test my knowledge. I hope I have been concise and provided enough detail. Thank you in advance! Your expertise on this forum is invaluable.
