Project task WBS selector and cost computing

[Virgule]

Hello friends,

I'm trying to create a sheet used for project estimation and i want it to be simple to use.

I want to be able to use buttons to create/delete tasks (cost item) as well as buttons to indent/outdent a task. The later should function like MSProject works. An indented task is now part of its parent category and the cost calculation is impacted as the parent task now sums all its children tasks and the total takes into account sub-totals not to sum same cost items twice. I would also like to have a WBS code that visually expresses that parent/child link.

Here is an example of a simple project estimation :

[TABLE="width: 500"]
<tbody>[TR]
[TD]WBS CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]COST (K$)[/TD]
[/TR]
[TR]
[TD]1.0[/TD]
[TD]CONSTRUCTION PROJECT[/TD]
[TD]1 120[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]

BUILDING​

[/TD]
[TD]840[/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]

EXCAVATION​

[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD]

CONCRETE​

[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]

PROCESS​

[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]1.2.1[/TD]
[TD]

PIPING​

[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]1.2.2[/TD]
[TD]

EQUIPMENT​

[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]1.3[/TD]
[TD]

ENGINEERING​

[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]1.3.1[/TD]
[TD]

PRELIMINARY​

[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]1.3.2[/TD]
[TD]

DETAILED​

[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


So let's go step by step.

1- How can I add buttons to indent and outdent a task ? This would add/delete a visual text indent, would have impact on the WBS code

2- How can I program parent tasks lines to bold when they are detected as containing child tasks as well as cost calculation being a summation of the contained tasks ?

3- How can I add buttons to create and delete tasks. Creating a task would for example insert a line under the cell selected with a sample task name ("TASK NAME") and current indentation of the above line. Deleting a task would delete the line of the cell selected and adjust the cost calculation and WBS codes of what's following. (ex : deleting task 1.3.4 would rename 1.3.5, 1.3.6, ... accordingly).

Any help is appreciated.

Regards,

 

[Virgule]

Ok so I started with create a task button. Here is the macro I have so far.


Sub Ajouter_tache()
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(ActiveCell.Row, 2) = "DESCRIPTION"
End Sub


Problems so far :
- Doesn't add a line but, only a cell;
- Add cell at the current selection shifting selected cell down, rather than a line under selected cell;
- Changes the content of column 2 only when selected cell is at column 2+, doesn't when selected cell is in column 1

 

[Virgule]

Fixed problems above

Sub Ajouter_tache()
ActiveCell.Offset(1).EntireRow.Insert
Cells(ActiveCell.Row + 1, 2) = "DESCRIPTION"
End Sub


Working on deleting next.

 

[Virgule]

Deleting rows is easy :

Sub Supprimer_tache()
Rows(ActiveCell.Row).Delete Shift:=xlShiftUp
End Sub

 

[Virgule]

Indent and Outdent done :

Sub Indent()
Cells(ActiveCell.Row, 3).IndentLevel = Cells(ActiveCell.Row, 3).IndentLevel + 1
End Sub


Sub Outdent()
Cells(ActiveCell.Row, 3).IndentLevel = Cells(ActiveCell.Row, 3).IndentLevel - 1
End Sub


Now I just need to figure out how to detect indent levels and format accordingly (putting the text in bold).

I'm a VBA beginner. Do I just write the code for the rest of the workbook in the same module as the macros outside of subs ?

 

[Virgule]

Any help is appreciated.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ect-task-wbs-selector-and-cost-computing.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Virgule]

Cross posted https://www.excelforum.com/excel-pr...ect-task-wbs-selector-and-cost-computing.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

I thought I only needed to post at one place, and so I did yesterday. Seeing that the thread is currently attracting eyes but no contribution yet, I did cross post right now. I'm currently actively working on this and was intending on updating both threads with my progress and solutions suggested. If both thread were to get suggestions I was to provide links to both. Sorry about not providing the thread link right away and thanks for notifying me of this rule.

 

[Virgule]

Ok, so let's be a bit more specific to focus potential contributors in a direction.

I'm thinking of having a set number of items (or lines) defined by a border formatting (bold border). I could therefore find the number of tasks/items by locating the border that possess that property :

Selection.Borders(xlEdgeBottom).Weight = xlMedium

Using the AddTask macro would then add 1 to that variable and the DeleteTask macro would substract 1. Am I onto something here ?