Hello:
I have what has turned into a complicated, but nice workbook, at least for our purposes, thanks to some direction from all of you. It is a project plan workbook with a GANTT chart that is pulling from 2 SQL databases and 4 other workbooks located on the network to update all of its tasks, statuses, and such from various teams. In order to stay updated it must refresh from time to time. Based on too many variables that could be impacted by a automatic update, I have decide to make this a manual process, all they have to do is click the button on the toolbar and it will refresh the data and update all the tasks.
I have placed some time stamps on a couple of sheets to indicate how long the data refresh process took and another to indicate when the plan was last updated. My issue is that I have placed a cell in the main plan view that will give the user a "heads up" when the plan should be updated, usually once its over 36 to 48 hours old, it should be updated at the longest. I can do this with a combination of a formula and conditional formatting (when the plan is under 24 hours old GREEN, 24-36 hours YELLOW, over 36 hours RED). It will also have a text cue as well "GREEN ="PLAN UPDATED", YELLOW = "PLAN REFRESH SUGGESTED", & RED = "REFRESH PLAN NOW". But the timer formula is static, it updates once on initial refresh and then only if you click it and tab out of it.
I need to use VBA, but trying to figure out how to translate this formula into VBA (I'm using minutes for testing right now instead of hours)
Data!$E$2 = "PLAN UPDATED"
Data!$E$3 = "PLAN REFRESH SUGGESTED"
Data!$E$4 = "REFRESH PLAN NOW"
Unless I'm wrong, using VBA will allow the cell to constantly check the age of the last refresh and update the cell accordingly. Am I wrong and can anyone help me get past this hurdle?
I have what has turned into a complicated, but nice workbook, at least for our purposes, thanks to some direction from all of you. It is a project plan workbook with a GANTT chart that is pulling from 2 SQL databases and 4 other workbooks located on the network to update all of its tasks, statuses, and such from various teams. In order to stay updated it must refresh from time to time. Based on too many variables that could be impacted by a automatic update, I have decide to make this a manual process, all they have to do is click the button on the toolbar and it will refresh the data and update all the tasks.
I have placed some time stamps on a couple of sheets to indicate how long the data refresh process took and another to indicate when the plan was last updated. My issue is that I have placed a cell in the main plan view that will give the user a "heads up" when the plan should be updated, usually once its over 36 to 48 hours old, it should be updated at the longest. I can do this with a combination of a formula and conditional formatting (when the plan is under 24 hours old GREEN, 24-36 hours YELLOW, over 36 hours RED). It will also have a text cue as well "GREEN ="PLAN UPDATED", YELLOW = "PLAN REFRESH SUGGESTED", & RED = "REFRESH PLAN NOW". But the timer formula is static, it updates once on initial refresh and then only if you click it and tab out of it.
I need to use VBA, but trying to figure out how to translate this formula into VBA (I'm using minutes for testing right now instead of hours)
Code:
IF(NOW()-I3<=TIME(0,4,59)+TIME(0,0,1),Data!$E$2,IF(AND(NOW()-I3<TIME(0,9,59)+TIME(0,0,1),NOW()-I3>TIME(0,4,59)+TIME(0,0,1)),Data!$E$3,IF(NOW()-I3>=TIME(0,9,59)+TIME(0,0,1),Data!$E$4,"")))Data!$E$2 = "PLAN UPDATED"
Data!$E$3 = "PLAN REFRESH SUGGESTED"
Data!$E$4 = "REFRESH PLAN NOW"
Unless I'm wrong, using VBA will allow the cell to constantly check the age of the last refresh and update the cell accordingly. Am I wrong and can anyone help me get past this hurdle?
