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.

20383


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]

:banghead: 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 (http://www.vbaexpress.com/forum/member.php?87-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.

[mdmackillop]

To explain my thinking.
There are six processes of varying period each of which contain sub-processes.
To determine the start date, deduct the total period of the six processes from the delivery date
Each following process start date is obtained by deduction of the total period of the remaining processes.
Write these six dates to the spreadsheet.
Within each process, the Worday Function is applied to the start date (or sub-process date) to determine the date of the following sub-process.
Sub-process dates applied by formula.
By entering dependent columns and period in Rows 2 & 3 (see image) relationships are clear and easily adjusted
A formula of =WORKDAY(INDIRECT(M$2 & ROW()),M$3) can be entered in M4 and copied to all required cells

[nathandavies]

SamT, your correct it it exactly like MS Project just built into excel hopefully.

[SamT]

SamT, your correct it it exactly like MS Project just built into excel hopefully.
Then build the Workbook to provide the data needed for a generic scheduler in a form that is best for Excel and VBA to use. See mdmackillop's post just above.

Then build the UserForm(s) to work with the Workbook.

Personally, I would use ListBoxes vs OptionButtons. One small advantage is that the first only requires code for each ListBox regardless of the number of options each has, and Listboxes can be hierarchically dependent. OptionButtons require code for every possible option, and they are hard to make interact.

Another UserForm Control to consider is the MultiPage. I can see eqach of your proposed OptionButton Frames replaced with a single ListBox on different MultiPage Pages. Each Page can have different Input and FeedBack Controls that allow the User for access and manipulate the date stored in the Worksheets.

Really I don't know enough about the VBA Project to make truly relevant suggestions.

[mdmackillop]

As Sam says, we don't know a lot about your project. I would suggest that you need to be clear about the interdependencies. Only those dates to be calculated from the form should appear on your form to be entered into the worksheet. If Group 4 start is X days after a Group 3 date then that can be entered as a calculated cell as above.

[nathandavies]

I agree with you both!

mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop), I only require 1 date either the Order Date or the Delivery Date from either of these dates all the other dates can be calculated based on the option buttons using the workday function. if the order date is used then the dates will be calculated from that date, if the delivery date is used then they should be calculated backwards. on you example you have the the number of days in row 2 which is exactly how i want it to work but i don't want that information on the main WIP sheet i would prefer if that was in the code or on another worksheet.

You exactly right with what your saving one group follows another group but some overlap as well.

[mdmackillop]

For simplicity, I would just hide Rows 2 & 3 but Rows 1 to 3 could be copied to another sheet (hidden if desired) and the formulae adjusted accordingly.

[nathandavies]

how would the period change based on the option button?

[Paul_Hossler]

Late to the game, but I'm trying to figure out your business rules

I took the Delivery date and backed up the days or weeks, and the order date and went forward

I did have to add durational tasks to my list so that I could see the start and end of Production, Metalwork, etc.

I assumed that all tasks were sequential, but I gather that there are some parallel tasks

I also assumed that all tasks were required, but I gather that some SW related ones might not be

I did not take into account weekends or holidays (the way MS Project does)


20425

This type of data presentation might help clarify your business rules consistently enough to implement in Excel, maybe adding columns for different scenarios.

[mdmackillop]

Here is something functional to play with

[nathandavies]

mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop), Thanks for the help on this i will have a play today and see how it runs etc.

Thanks

[nathandavies]

mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop), I have had a run with your example and it seems to work perfectly. I have changed the worksheet "prog" to suit my updated worksheet but i keep getting an error on this line of code and it doesn't seem to pick up the Project Number from the selected cell.


For Each cel In Cells(rw - 1, 12).Resize(, 45).SpecialCells(xlCellTypeFormulas)20432

There are two small modifications i would like to make if possible;

1. I only want to include software engineering time is the check box is selected if it is not selected then the date is left blank. is that possible?
2. I want to be able to use either the delivery date or the order date. at the minute you have to have a delivery date for the code to work

I have attached my full workbook for you to have a look at so you can see what i have changed.

Thanks for your continued help on this!!

[mdmackillop]

Modified code attached. The code requires formulae in Row 2 which can be copied down; this row would be best hidden. Mods 1 & 2 incorporated.

[nathandavies]

It wont work with it been in Row 2, i have formatting code so as soon as you put a project number in A2 it moves it to A3, would it be possible to use the very last row (1048576) ?

Also i cant seem to get it to select the Project Number when highlighting the cell?

[mdmackillop]

The formulae can be copied from the Prog sheet .

Also I can't seem to get it to select the Project Number when highlighting the cell?
Can you clarify. The code is written to: Select ProjNo, Click "Program Dates"

[nathandavies]

Found the mistake, i'd not dim MyProjNo correctly.

This is the line where it errors all the time.


For Each cel In Cells(rw - 1, 12).Resize(, 45).SpecialCells(xlCellTypeFormulas)

[mdmackillop]

You'll need to adjust some of the formulae on Prog.

[nathandavies]

Hi mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop),

Sorry for the delayed response i have been away for 12 days.

I have played around with the prog and got everything working as i would like it to only thing i have noticed is it is copying the formula into the WIP worksheet from Prog, is there now way of only copying the date figures and not the full formula?

I'm also finding that the excel worksheet crashes a lot on opening any reason this would happen?

[mdmackillop]

Change the copy code to PasteSpecial xlValues

[nathandavies]

mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop)

Is this the code to change


'Write from form to WIPPrivate Sub CommandButton1_Click()
Dim StartDate, rw, cel

StartDate = CDate(tbDelivery) - 7 * (CInt(frEngineering.Tag) + CInt(frApproval.Tag) + CInt(frSoft.Tag) + CInt(frComp.Tag) + CInt(frMetal.Tag) + CInt(frProduction.Tag))
'Fill in start date
With Sheets("WIP").Columns(1).Find(tbProj)
.Offset(, 11) = CDate(StartDate)
rw = .Row
'Fill in formulas
Sheets("Prog").Range("M4:AY4").PasteSpecial xlValues("WIP").Cells(rw, 13)
End With

Call Summary
End Sub

[mdmackillop]

'Fill in formulas
Sheets("Prog").Range("M4:AY4").Copy
Sheets("WIP").Cells(rw, 13).PasteSpecial Paste:=xlPasteValues

[nathandavies]

I seem to be having a problem, the dates are not adjusting they are staying on 03-01-1900.

i have attached the workbook so you can see. i have highlighted the one i was trying to program in orange you will see the start date is correct 21/08/17 but the rest are not.

[mdmackillop]

try this

'Fill in formulas
Sheets("Prog").Range("L4") = StartDate
Sheets("Prog").Range("M4:AY4").Copy

[nathandavies]

Thank you that is now working perfectly!

but found another problem when i only put in an order date i get an error on the line marked with **.



'Write from form to WIPPrivate Sub CommandButton1_Click()
Dim StartDate, rw, cel
***StartDate = CDate(tbDelivery) - 7 * (CInt(frEngineering.Tag) + CInt(frApproval.Tag) + CInt(frSoft.Tag) + CInt(frComp.Tag) + CInt(frMetal.Tag) + CInt(frProduction.Tag))***
'Fill in start date
With Sheets("WIP").Columns(1).Find(tbProj)
.Offset(, 11) = CDate(StartDate)
rw = .Row


'Fill in formulas
Sheets("Prog").Range("L4") = StartDate
Sheets("Prog").Range("M4:AY4").Copy
Sheets("WIP").Cells(rw, 13).PasteSpecial Paste:=xlPasteValues
End With
Call Summary
End Sub

[mdmackillop]

A superceded concept. Try this change

'Write from form to WIP
Private Sub CommandButton1_Click()
Dim StartDate, rw, cel
StartDate = CDate(lblResult)

[nathandavies]

That's it completed!!

All now working as it should! excellent help and job on this!!