We help IT Professionals succeed at work.

Date Model Worksheet part Tres

Bright01
Bright01 asked
on
I have this workbook/sheet that was created by EE Professionals that I'm now looking for another enhancement to.

Overview:  This Sheet is part of a model to help with scheduling but is not intended to be a replacement for MS Project.  When you pick a date horizon (Start/End) it auto sets the model up.  The next step here is to add in Tasks.  There are 3 types of tasks (Parallel (P), Critical (C) and Milestone (M) - The Milestone must be a single date.

What I'm looking for:  I'm looking for a way to have a macro or formula (since the sheet is dynamic (it can expand or contract), I think a macro may be a better way to go, take a task and the identified "type" and set it in the appropriate color and space based on the date.  Mocked up version attached.

Much thanks in advance,

B. AutoDateFormatv6.xlsm
Comment
Watch Question

SANTABABYSoftware Professional
BRONZE EXPERT

Commented:
Attached is the file with updated macro (added new code, that is called when update button is clicked). Please test and let me know.
AutoDateFormatv6.xlsm

Author

Commented:
Santababy,

Thank you for the quick reply.  Take one of the tasks and change the dates.  You will see that the macro does not clear the original date.  Take a look.

B.
SANTABABYSoftware Professional
BRONZE EXPERT
Commented:
The cells was not gettong cleared before repainting. Fixed it. Attached.
AutoDateFormatv6.xlsm

Author

Commented:
Santababy,

Excellent!  I have to ask if you can tweek this; I recognize this is outside the original request but as you can imagine, other things do come up..... If you like I will close this out and ask a related question...here are two related issues, if the "type" is "M" then it should treat it as a single date.   This means that if you enter a date in Start, it automatically populates the same date in the End Date.  Also, if you put in a related "task" (same number/format) then it should recognize the milestone is associated with the appropriate task and add it in the same line as either a "P" or "C" Task.

Let me know if it's an easy fix or if I should close this out........ much thanks for sticking with me on this.

B.
B,
I've added an extra function for the first part of this new request: testForMilestone. It's a one liner so definitely counts as easy.

It is called by the same function that calls the calendar (in the worksheet module) and simply looks into the cell offset by 1 column to the left and checks for type, then auto-populates the date of the cell 1 column to the right if this is "M".

I'd suggest that as the second part is outside the original that you should post this as a new question, unless Santababy comes back with an update shortly.
Regards,
Philip

 AutoDateFormatv6.xlsm

Author

Commented:
SA,

Thanks for the "liner" and I'll close this out.  One thing, can you make it so that when you put in an "M", when you put in the date (Start) it auto files the End?  The way it's set up, I have to use the calendar function for it to autopopulate.

Much thanks,

B.
B,
Updated using worksheet_change event and same logic as before. Enjoy.
Regards,

AutoDateFormatv6.xlsm

Author

Commented:
SA,

Thanks for the adjustment.  I still can't get it to work right.  If you put in a new Task, you get a runtime error on line;

If Target.Cells.Offset(0, -1) = "M" Then Target.Cells.Offset(0, 1) = Target.Cells

Also, when using "M", it has to have the same Task name in Col. A in order to know if it adds it to an existing Row or create a new one.

Sorry for the confusion.

B.
B,
Oops, you're right, I forgot to account for it looking in column 1, since we know you only want this to handle dates for  (at the moment) I have put in a column check on it.
I didn't look at the 2nd part yet, but will do so if I get a moment this afternoon.
Regards,

AutoDateFormatv6.xlsm
And I had a quick look at the code provided by SantaBaby and it already does what I think you are describing in terms of adding type "M" to the same row as an existing task...unless I'm misunderstanding what you require?

E.g. if I look at row 15 it has Type M task 2, this automatically colours the date for that row in on the first row for task 2 i.e. row 12. Is that not what you wanted?

I'm not sure that I agree with where it is adding this in, for instance the "Update" button creates the dates and colour codes the weeks, but this is being done after the colour-coding has been added. I would use a separate button and call the colour-coding on demand, or at least after all dates have been populated. And potentially straight after you have added a new task/date combo.
Regards
B,
Assuming the above I have amended all the code slightly for you. The initial calendar set up will clear all formatting from line 6 to the end of the workbook; if there are already project dates it wil re-colour these for you.
As a second function if you add a new task, once you include the 2nd date it will auto- colour code the new row. Obviously this means that for type "M" it will both auto-populate the 2nd data and auto- colour-code the relevant cell.
Regards,

 AutoDateFormatv6.xlsm

Author

Commented:
Santababy and SubversiveArmadillo,

Great Teamwork!  You guys really jumped in to get this done and exceptionally well.  It generally takes a few itterations as you know and you guys worked like a Team to accomplish this request.  Santababy, you put together the major portion of this and then SA, you tweeked it and then added to it.  In the end the final product was the best solution; however, I gave the majority of the points to SB since he did the initial "heavy lifting".  Hope you guys find that fair.

Again, great Teamwork and much thanks,

B.
Perfectly fair, and your logic is impeccable.

Anyway, it's not about the points, it's about you having an answer that works - although points are nice too :-)
SANTABABYSoftware Professional
BRONZE EXPERT

Commented:
Appreciate your note. Sorry, I had to staye away to take care of some work (that actually brings my bread and buter) and could not repond your postings today. But, at the end all that matters is you problem is addressed. Thanks to SA for making it happen.

Regards, SB

Explore More ContentExplore courses, solutions, and other research materials related to this topic.