Create a Project Scheduler With Excel

[nathandavies]

Hi All,
I'm after some help writing some code to input a range of dates using a userform.

On the userform i have two text boxes 1. Project number (which relates to column A) 2. Delivery date (which relates to column AW) and a range of option buttons with different program lengths. (2weeks / 4weeks etc). i'm wanting to automatically input the dates working back from the delivery date only using workdays (Mon-Fri) based on which program has been selected using the option buttons.

if no delivery date is inputted then it will automatically program an 8 week schedule from the days date.

if there is no software required (checkbox option) it will input "N/A" in the date boxes on the following columns O,Q,Y,AK,AM,AS,AZ,

i have attached my work book for your assistance and filled out the dates so you can see what i'm trying to achieve.

wip_1.xlsm


Thank ND

[SamT]

different program lengths. (2weeks / 4weeks etc). i'm wanting to automatically input the dates working back from the delivery date only using workdays (Mon-Fri) based on which program has been selected

NumberOfIntervals = 5 * ProgramLength 'In 5 day weeks
DesiredDate = DateDif("w", (-1) * NumberOfIntervals, DeliveryDate)

'   "w" for Workdays, (-1) * to Subract.

Set Delivery Date to n weeks from Today, where Program Length value = n

NumberOfIntervals = ProgramLength 'In weeks
DeliveryDate = DateDif("ww",  NumberOfIntervals, Date)

'   "ww" for calendar weeks.

The interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

Remarks
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.

[nathandavies]

SamT, I'm not sure how to even start this!

I now what intervals I require to work back from delivery date but not sure how to create the code

[SamT]

Sorry, My Excel XP failed to open that attachment. And I need to see it to help you.

Let me change the Thread Title to something more interesting to see if someone else can help.

[nathandavies]

SamT, Thats great Thanks.

I have attached a new workbook with some changes to hopefully make it a lot easier. on the user form there are now 6 sets of option button's Engineering (RED), Approval (Green), Software (if required check box), Components (purple), Metalwork(Blue) & Production (yellow). I filled in the dates using the workday function so you can see how i'm trying to populate the workbook using the userform.

Please see my attached update workbook and a snippet of the userform filled out.

[mdmackillop]

Give your controls meaningful names; makes understanding and use much easier.
Use the option buttons to set the tag value for each group
Use the Group tag as the value to input.

Private Sub obEng1_Click()
    If obEng1 = True Then frEngineering.Tag = DateValue(tbDelivery) - 7
End Sub


Private Sub obEng2_Click()
    If obEng2 = True Then frEngineering.Tag = DateValue(tbDelivery) - 14
End Sub


Private Sub CommandButton1_Click()
    Columns(1).Find(tbProj).Offset(, 16) = CDate(frEngineering.Tag)
End Sub

[mdmackillop]

Alternative code

Private Sub obEng1_Click()
   Call Eng(1)
End Sub


Private Sub obEng2_Click()
    Call Eng(2)
End Sub


Private Sub Eng(Wk)
    If Me.Controls("obEng" & Wk) = True Then frEngineering.Tag = DateValue(tbDelivery) - Wk * 7
End Sub

Private Sub CommandButton1_Click()
    Columns(1).Find(tbProj).Offset(, 18) = CDate(frEngineering.Tag)
End Sub

[nathandavies]

mdmackillop, I think i've got a better understanding now, i will have a good and update my workbook shortly!

Thanks for your help on this!

[nathandavies]

not sure what i need to write for this bit of code

frEngineering.Tag

[nathandavies]

Ignore my last post! I have worked it out!

[nathandavies]

I've managed to work out have to put my first date it based on the order date but from there i'm not sure how you continue with the dates, this is the formula i would like to use just not in code.

=WORKDAY(Start Date, 1 Week program obEng1)

I have attached my sheet with the formulas inserted so you can see what i'm trying to achieve

[mdmackillop]

For clarification
Each frame group will have a start date based on the Delivery date less the number of weeks indicated by Option Button, with following dates using Workday function. i.e. Approval is not directly dependent on Engineering.
If so, CommandButton1 code will be expanded to include a line for each frame.

Suggestion:
On a separate sheet, the userform can record a simple table reflecting the 8 values applicable to each Project Number
e.g.
A1234,01/09/17,10/09/17,2,3,2,5,8,10
This will allow you to populate the Userform from previously entered data for editing or simple inspection

[nathandavies]

I have not thought this through correctly. Each frame group would have to be concurrent of the last so the last engineering date then goes to first approval date etc. the total program length would be each frame group added up to meet the delivery date.

EG.

Eng = 1 week - 5 days
Approval = 1 week - 5 days
software = N/A
components = 2 weeks- 10 days (based on approval date)
metalwork = 6 weeks - 30 days (based on approval date)
Production = 2 weeks - 10 days (Based on either metalwork and components required by date each ever is last date)

Delivery Date - 12 weeks/60 days is the start date

The problem then is that each frame has a number of days for each cell so based on a 1 week program it would look something like this

General Arrangement Issued = 1 Day from Start
Power Section Drawings Issued = 2 Day from Start
BMS/Control Drawings Issued = 2 Day from Start
Mechanical Design Completed = 5 Day from Start

[nathandavies]

So if the Start date was 19-09-2017 (tbORDER can be used for this) this is an example of how each cell would be worked out if all OB was selected to 1 week, obviously if it was longer they would be multiplied up

1 Week Example

General Arrangement Issued = 1 Day from Order Review (Start Date)
Power Section Drawings Issued = 2 Day from Order Review (Start Date)
BMS/Control Drawings Issued = 3 Day from Order Review (Start Date)
Mechanical Design Completed = 5 Day from Order Review (Start Date)
Drawing Approval Required By = 5 Day from either Power Section or BMS/Control Section Date
I/O Schedule Issued = 5 Day from Order Review (Start Date)
FDS Issued = 5 Day from Order Review (Start Date)
Metalwork Ordered = 1 Day from Drawing Approval Required By Date
Metalwork Required By = 5 Day from Metalwork Ordered Date
All Electrical Parts Ordered = 1 Day from Drawing Approval Required By Date
Electrical Parts Required By = 5 Day from All Electrical Parts Ordered Date
PLC Parts Ordered = 1 Day from Drawing Approval Required By Date
PLC Parts Required by Production = 5 Day from PLC Parts Ordered Date
Production Masters Issued = 2 Day from Drawing Approval Required Date
Tags & Terminal Numbers Required By Production = 1 Day from Production Masters Issued
Method Statement Issued = 10 Day before Delivery Date
Electrical Test = 7 Day before Delivery Date
Software Integration Test = 5 Day before Delivery Date
CFAT = 5 Day before Delivery Date
Dispatch = 1 Day before Delivery Date
Delivery = 1 Day After Dispatch Date.

i have attached a workbook filled out with the workdays as above so you can see what i'm trying to acheive

[mdmackillop]

Here something to look at (made prior to your last post)

[nathandavies]

I've had a look at your code, but i don't follow what you have done.

i tried to code and it didn't populate any dates, it came up with an error code after first run for some reason wouldn't open the frmProgramme.

[mdmackillop]

A couple of tweaks; File reposted above. The UF should partially populate from ufData on opening. Basic dates should fill the 2 Blue cells with others adjusted as per Row 2. These are for example only, I've not attempted to follow your adjustments.

[nathandavies]

I'll have a look and see if I can work it out. Thanks.

[nathandavies]

Can't get my head around the code, think I'm going to change the way it's populated.

[SamT]

I have not thought this through correctly. Each frame group would have to be concurrent of the last so the last engineering date then goes to first approval date etc. the total program length would be each frame group added up to meet the delivery date.


The problem then is that each frame has a number of days for each cell so based on a 1 week program it would look something like this

General Arrangement Issued = 1 Day from Start
Power Section Drawings Issued = 2 Day from Start
BMS/Control Drawings Issued = 2 Day from Start
Mechanical Design Completed = 5 Day from Start

This is beginning to sound more like a task for MS Project than MS Excel.