Update the date, but not everyday

[jdnels81]

This would seem like something so many people would want, I'm very surprised that, after scouring the internet, I couldn't find anything that adresses this issue. First of all, I'm totally inexperienced with VBA, but I'm experienced in C, C++, MATLAB and the like, so don't worry about scaring me with tech, I'm simply trying to figure out how to do this without first spending weeks or months learning VBA on my own before attempting to implement this.

So, what I am trying to do is have, on a single spreadsheet, a list of tasks with due dates, however, the due dates change. They change because after a due date has passed, you have a new due date. I don't want to have pages and pages of task sheets, just one task sheet, and I don't want to manually change the due dates every week or every month, etc. I want to, once I enter in a start date, all these due dates will automatically update to the next due date based on if today's date has surpassed the due date. It will compile all the different tasks in this program, some are due weekly, some are due once every other week, others monthly, some quarterly, and other annually. They will all be on the same task sheet and once the due date has passed, the value in the "Due Date" cell will need to change to a new due date. I included a picture to give you an idea of the template. Don't worry about the conditional formatting, I have that already figured out. All I'm concerned with is having the due date replaced by a new value once today's date has passed the due date. Thanks! Screen Shot 2012-10-25 at 3.41.18 PM.jpg

[Sindhus]

You can try code in this structure,

Please Login or Register  to view this content.

Date returns today's date.

[jdnels81]

Ok, I'll try something like that. I might have to suck it up and spend a few weeks learning how to code in VBA in order to make this work. I'll need to code for each individual cell, I can't use an iterative "For" because I won't be grouping weekly or monthly tasks together, they'll be grouped by what system they're associated with instead. Thanks!

[jdnels81]

Ok, I have another question for you guys. I set a workbook open event so that this code will execute anytime the workbook opens. The only problem is that the VBA editor/debugger opens every time I open excel. This happens if I do it using Auto_Open subroutine and if I put the code in the private module. The code works, it just opens the editor/debugger, but it doesn't indicate any problems with the code or give me any errors, it just opens is like a pop-up. The code I'm using in the private module is as follows:

Please Login or Register  to view this content.

[jaslake]

Hi jdnels81

The Workbook_Open event should be in ThisWorkbook Module.

Change the code to

Please Login or Register  to view this content.

[jdnels81]

It was in the ThisWorkbook Module. From what I understand, that is what is meant by "private module" nowadays. However, it didn't stop the problem. I removed the code, and surely enough, it didn't automatically open it, but when I run my "Macro1" macro manually, it also automatically pops up the editor, yet gives me no errors or reason why it would open the editor. So perhaps it's the Macro1 code that's causing the editor to pop up. Here's my code for the Macro

Please Login or Register  to view this content.

[jaslake]

Hi jdnels81

With this code in ThisWorkbook

Please Login or Register  to view this content.

and this code in a General Module

Please Login or Register  to view this content.

The workbook opens and changes Cells(2,5) of Sheet1 (and does not open the VBA Editor).

[jdnels81]

That works beautifully! Was it the "." before the "Cells" or the "with" statement that fixed it you think? I've never seen a "with" statement before with C++, so I had to look up what it does after you posted that.

Anyway, I have one last question and I think I'm done here. I'd like to see if I could get this macro to run automatically once a day without having to use task scheduler. I looked at some forums, namely here at the link provided, but their advice didn't seem to help. Any ideas? http://www.pcreview.co.uk/forums/run...y-t940041.html

[jaslake]

Hi jdnels81

Regarding this

Was it the "." before the "Cells" or the "with" statement that fixed it you think?

Your code didn't specify what worksheet the code should run on...I suspect that was the issue. The . before Cells merely ties the Cells to Sheet1.

Regarding this

Please Login or Register  to view this content.

The only way I'm familiar with to do this is to leave the Workbook open at all times and create an OnTime procedure to run the code every 24 hours. There are ways to open excel with Batch Files such that it can be scheduled to open. However, I'd assume you'd have to respond to "Allow Macros" unless you set Security to Low. Not a good idea.

[jdnels81]

Well, hopefully the brutes known as users will understand that they'll have to close and reopen this every time. Thanks for the help!!

[jaslake]

Hi jdnels81

I made a misstatement here

leave the Workbook open at all times

Tell me what you wish to do...perhaps we can think it through.

[jdnels81]

Well, the problem is that the users might leave the workbook open at all times, and they might not. So, I'd like to cover both grounds, have it check for the need to update every time it opens, and also check for updating once a day just in case they never close it. Some of these guys are really smart, and some of them....well, let's just say some didn't know how to use a mouse before starting this job. So instructing them on how to set it up in task scheduler doesn't seem plausible. I just want to send them this spreadsheet and say "here, use this, put in the completion dates and email me an updated copy every week". If I could do that without introducing a serious computer vulnerability, that would be awesome.

[jaslake]

Hi jdnels81

Let me play with it a bit...what time would you like the procedure to run...like 1:00 AM?

[jdnels81]

The time is irrelevant. 1AM is good, anytime shortly after midnight is fine. Thanks!

[jaslake]

Hi jdnels81

I'm unable to make this work without getting the Enable Macros message. If the Workbook IS open then the procedure will execute as desired.

You may wish to start a new Thread with this issue.

[jdnels81]

If the Workbook IS open then the procedure will execute as desired.

I'm confused. Were you able to get it to work only while workbook is open BUT you had to enable macros? I don't need this to run while the workbook is closed. I just need it to run whenever the workbook is manually opened and just in case the workbook is left open. If I have to enable macros to do that, just let me look at the code, and I'll consider it. Thanks, John!

[jaslake]

Hi jdnels81

This code is in the attached.

In ThisWorkbook

Please Login or Register  to view this content.

In a General Module

Please Login or Register  to view this content.

The code will run Macro1 upon opening the Workbook and then schedule Macro1 to run at 1:00 AM each day until the Workbook is closed. It does this repeatedly...let me know of issues.

[jdnels81]

Thanks, I'll mark this solved, but how do you test the timer code besides waiting until 1AM? I tried several times to change the time to a few minutes later, but the data doesn't update.

[jaslake]

Hi jdnels81

When you

I tried several times to change the time to a few minutes later

Did it "Beep"? If it did the code executed. The code won't update unless

Please Login or Register  to view this content.