Project-Style (WBS) Numbering in MS Excel

published on

If you know why you’re reading this already, skip to the code. Otherwise, here’s some explanation. In a project Work Breakdown Structure (WBS), tasks are organized into major tasks, sub-tasks, sub-sub-tasks, etc. as in the following example which is an actual project plan used by NASA:

Project plan screenshot

Instead of sequentially numbering the tasks, we assign subtask numbers to those tasks that roll up under other tasks. Assemble spaceship is a subtask of Build a spaceship so it gets Build a spaceship‘s number (1) plus a subtask number (3, since it’s the third subtask) so its WBS number is 1.3. Attach top part to middle part is a subtask of Assemble spaceship, so it gets 1.3 plus a sub-subtask number (1.3.1), and so on. MS Project also bolds any items with subtasks.

Since this type of WBS or outline numbering functionality isn’t available in Excel, it requires a VBA macro.

Notes

1.
Here is a sample spreadsheet, complete with the code and a command button, for those who want something ready-to-use.
2.
Tested in Excel 2003 and 2007 for Windows.
3.
The macro is based on the concept that the indentation of a particular task in column B dictates its WBS “depth” (whether it’s a task, sub-task, sub-sub-task, etc.). WBS numbering will be created in column A.
4.
The macro renumbers everything at once, so after you add this macro to your project’s VB code, create a button or other control that runs the macro.
5.
It requires certain formatting. Read all of the formatting assumptions, commented at the top of the macro.

VBA Code for WBS numbering in Excel

Sub WBSNumbering

'Renumber tasks on a project plan
'Associate this code with a button or other control on your spreadsheet

'Layout Assumptions:
'Row 1 contains column headings
'Column A contains WBS numbers
'Column B contains Task description, with appropriate indentation
'Some text (here we assume "END OF PROJECT") delimits the end of the task list

    On Error Resume Next

    'Hide page breaks and disable screen updating (speeds up processing)
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    'Format WBS column as text (so zeros are not truncated)
    ActiveSheet.Range("A:A").NumberFormat = "@"
    Dim r As Long                   'Row counter
    Dim depth As Long               'How many "decimal" places for each task
    Dim wbsarray() As Long          'Master array holds counters for each WBS level
    Dim basenum As Long             'Whole number sequencing variable
    Dim wbs As String               'The WBS string for each task
    Dim aloop As Long               'General purpose For/Next loop counter

    r = 2                           'Starting row
    basenum = 0                     'Initialize whole numbers
    ReDim wbsarray(0 To 0) As Long  'Initialize WBS ennumeration array

    'Loop through cells with project tasks and generate WBS
    Do While Cells(r, 2) <> "END OF PROJECT"

        'Ignore empty tasks in column B
        If Cells(r, 2) <> "" Then

           'Skip hidden rows
            If Rows(r).EntireRow.Hidden = False Then

                'Get indentation level of task in col B
                depth = Cells(r, 2).IndentLevel

                'Case if no depth (whole number master task)
                If depth = 0 Then

                    'increment WBS base number
                    basenum = basenum + 1
                    wbs = CStr(basenum)
                    ReDim wbsarray(0 To 0)

                'Case if task has WBS depth (is a subtask, sub-subtask, etc.)
                Else

                    'Resize the WBS array according to current depth
                    ReDim Preserve wbsarray(0 To depth) As Long

                    'Repurpose depth to refer to array size; arrays start at 0
                    depth = depth - 1

                    'Case if this is the first subtask
                    If wbsarray(depth) <> 0 Then

                        wbsarray(depth) = wbsarray(depth) + 1

                    'Case if we are incrementing a subtask
                    Else

                        wbsarray(depth) = 1

                    End If

                    'Only ennumerate WBS as deep as the indentation calls for;
                    'so we clear previous stored values for deeper levels
                    If wbsarray(depth + 1) <> 0 Then
                        For aloop = depth + 1 To UBound(wbsarray)
                            wbsarray(aloop) = 0
                        Next aloop
                    End If

                    'Assign contents of array to WBS string
                    wbs = CStr(basenum)

                    For aloop = 0 To depth
                        wbs = wbs & "." & CStr(wbsarray(aloop))
                    Next aloop

                End If

                'Populate target cell with WBS number
                Cells(r, 1).Value = wbs

                'Get rid of annoying "number stored as text" error
                Cells(r, 1).Errors(xlNumberAsText).Ignore = True

                'Apply text format: next row is deeper than current
                If Cells(r + 1, 2).IndentLevel > Cells(r, 2).IndentLevel Then

                    Cells(r, 1).Font.Bold = True
                    Cells(r, 2).Font.Bold = True
                'Else (next row is same/shallower than current) no format
                Else
                    Cells(r, 1).Font.Bold = False
                    Cells(r, 2).Font.Bold = False
                End If
                'Special formatting for master (whole number) tasks)
                If Cells(r, 2).IndentLevel = 0 Then
                    Cells(r, 1).Font.Bold = True
                    Cells(r, 2).Font.Bold = True
                    'Add whatever other formatting you want here

                End If

            End If

        End If

    'Go to the next row
    r = r + 1

    Loop

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

Sub WBSNumbering

'Renumber tasks on a project plan

'Associate this code with a button or other control on your spreadsheet

'Layout Assumptions:

'Row 1 contains column headings

'Column A contains WBS numbers

'Column B contains Task description, with appropriate indentation

'Some text (here we assume "END OF PROJECT") delimits the end of the task list

On Error Resume Next

'Hide page breaks and disable screen updating (speeds up processing)

Application.ScreenUpdating = False

ActiveSheet.DisplayPageBreaks = False

'Format WBS column as text (so zeros are not truncated)

ActiveSheet.Range("A:A").NumberFormat = "@"

Dim r As Long 'Row counter

Dim depth As Long 'How many "decimal" places for each task

Dim wbsarray() As Long 'Master array holds counters for each WBS level

Dim basenum As Long 'Whole number sequencing variable

Dim wbs As String 'The WBS string for each task

Dim aloop As Long 'General purpose For/Next loop counter

r = 2 'Starting row

basenum = 0 'Initialize whole numbers

ReDim wbsarray(0 To 0) As Long 'Initialize WBS ennumeration array

'Loop through cells with project tasks and generate WBS

Do While Cells(r, 2) <> "END OF PROJECT"

'Ignore empty tasks in column B

If Cells(r, 2) <> "" Then

'Skip hidden rows

If Rows(r).EntireRow.Hidden = False Then

'Get indentation level of task in col B

depth = Cells(r, 2).IndentLevel

'Case if no depth (whole number master task)

If depth = 0 Then

'increment WBS base number

basenum = basenum + 1

wbs = CStr(basenum)

ReDim wbsarray(0 To 0)

'Case if task has WBS depth (is a subtask, sub-subtask, etc.)

Else

'Resize the WBS array according to current depth

ReDim Preserve wbsarray(0 To depth) As Long

'Repurpose depth to refer to array size; arrays start at 0

depth = depth - 1

'Case if this is the first subtask

If wbsarray(depth) <> 0 Then

wbsarray(depth) = wbsarray(depth) + 1

'Case if we are incrementing a subtask

Else

wbsarray(depth) = 1

End If

'Only ennumerate WBS as deep as the indentation calls for;

'so we clear previous stored values for deeper levels

If wbsarray(depth + 1) <> 0 Then

For aloop = depth + 1 To UBound(wbsarray)

wbsarray(aloop) = 0

Next aloop

End If

'Assign contents of array to WBS string

wbs = CStr(basenum)

For aloop = 0 To depth

wbs = wbs & "." & CStr(wbsarray(aloop))

Next aloop

End If

'Populate target cell with WBS number

Cells(r, 1).Value = wbs

'Get rid of annoying "number stored as text" error

Cells(r, 1).Errors(xlNumberAsText).Ignore = True

'Apply text format: next row is deeper than current

If Cells(r + 1, 2).IndentLevel > Cells(r, 2).IndentLevel Then

Cells(r, 1).Font.Bold = True

Cells(r, 2).Font.Bold = True

'Else (next row is same/shallower than current) no format

Else

Cells(r, 1).Font.Bold = False

Cells(r, 2).Font.Bold = False

End If

'Special formatting for master (whole number) tasks)

If Cells(r, 2).IndentLevel = 0 Then

Cells(r, 1).Font.Bold = True

Cells(r, 2).Font.Bold = True

'Add whatever other formatting you want here

End If

'Go to the next row

r = r + 1

Loop

End Sub

Nick Leeder says:

April 6, 2009 at 8:07 am

Hi,
This macro works brilliantly except for one slight flaw with the numbering. When you get to ten sub items (or multiples of ten) e.g. 7.10, because of the way it is set up on numbers, it returns 7.1, thus duplicating the the first sub item (7.1).
Any ideas on how you stop the macro or excel doing this?

Jeremy says:

April 6, 2009 at 8:56 am

Hi Nick — I am amazed that someone found a use for this so quickly and I’m really glad it works for you. Thanks for the question; try formatting the WBS column as text and then re-running the numbering macro.

When I get a chance I’ll go back and add that action into the macro; I should have included it either as an assumption or in the code. As you might have seen, the macro already removes “Number Stored As Text” errors because it’s assuming that the column is in text format.

Update — I think I fixed this. Let me know if the new macro successfully eliminates this issue.

Dan says:

June 1, 2009 at 9:09 am

Thanks so much for this. It really helped out on a project at work.

Scott says:

June 11, 2009 at 10:29 am

Very nice code! The next phase, it seems to me, is to run through and set row groupings based on the WBS, so that the whole thing really does become a expandable/collapsable project plan.

Brent says:

June 25, 2009 at 12:15 pm

I agree with Scott…any ideas on how to auto-group (outline)? I’ll do some googling and see if I can see if someone else has done something like this.

June 25, 2009 at 12:38 pm

Thanks Scott & Brent. I think that’s a good suggestion. I was looking into how to do it and I think the easiest way is Group & Outline (http://office.microsoft.com/en-us/excel/HP052016411033.aspx) but it’s kind of ugly so, as usual, I’m going to see if I can find a more complicated solution. Pretty busy right now but this is on my to-do list. If one of you figures it out first, please let me know and I can post an update with your contribution.

Mark says:

September 7, 2009 at 7:42 pm

This a great tool but not having a programming background are there any steps for me as a layman to take to program this onto my computer? May be a stupid question… not really sure.
Thanks

September 8, 2009 at 7:46 am

Hi Mark. Try this: http://j.modjeska.us/data/wbs_numbering.xlsm. I hope this helps.

Chris says:

October 11, 2009 at 7:49 am

Thank you very much for posting this, especially the xlsm download. Your contribution is going to be a key factor in the success of a project that I am working on.

Have you looked at an Earned Value Management supplement to this macro?

Thanks again

Martin says:

October 12, 2009 at 4:45 am

AWESOME!!! Thank you very much.

Ellerd says:

October 15, 2009 at 11:22 am

Great macro. One question left, i also don’t have any knowledge of programming. What do i need to delete or modify in the Macro, if i only want that level 1 items will be bold?

October 16, 2009 at 9:14 am

Cheers Chris, Martin, and Ellerd.

Chris – my long-term goal is to develop this into a much more comprehensive project management tool. Earned value management would definitely be a nice feature so I’ll add that to the list. Unfortunately life is getting in the way of my little programming projects these days and I probably won’t be making much progress on this for a while. Collapsible task groups are the next thing on the list.

Ellerd – first, unbold all your current lines on the project. Then comment out what appear above as lines 95-103 in the code above and re-run the macro. To comment out a line, just put a single quote mark at the beginning, like you see on line 94 (once commented it will turn green). You could also just remove those lines if it’s easier.

I think that will accomplish what you’re after. If not, let me know.

Guy says:

December 21, 2009 at 1:34 pm

Jeremy,

Thanks a ton for this macro. I would like for it to “Do Unitl” if finds two blank cells in succession. I can make it work if only looking for first blank cell with Not IsEmpty(Cells(r, 3)). I have seen this before but cannot figure it out.

Thanks in advanced.

Guy

Tony Wright says:

January 25, 2010 at 5:39 pm

Brilliant piece of work – many thanks for making it available to us non-VBA types.

Tony

Etienne says:

April 20, 2010 at 10:38 am

Hi Mark, from a real dummy,
Tried http://j.modjeska.us/data/wbs_numbering.xlsm, what next? Extracted the files does ot seem to work?

Hope I dont insult you.

Regards

April 20, 2010 at 11:24 am

Guy – Only 6 months late here with your answer, but here it is anyway because I think it’s a good idea.

This will eliminate the need for an “END OF PROJECT” flag, and instead it will stop numbering when it encounters two successive blank rows. This assumes your task descriptions are in Column B. If your task descriptions are in a different column, change the number 2 in the code below to the number corresponding to the desired column (C=3, D=4, etc.). I think the IsEmpty function might be Office 2007+ only, so YMMV on other versions.

Do While Not (IsEmpty(Cells(r, 2)) And IsEmpty(Cells(r + 1, 2)))

Hope that helps.

Etienne – Now sure what you’re asking, but the file is for Excel 2007. It may not work on other versions.

Tom says:

April 29, 2010 at 12:17 pm

I downloaded this expecting a .xls or .xlsx file but got a zip file which expanded to a lot of files, but none that are Excel files. What am I missing? I guess I could retype the macro by hand from the screenshot but that seems error-prone. I have Excel 2007. In any case, thanks for making this available.

April 29, 2010 at 7:33 pm

Tom: The only file linked here is a .xlsm (Macro-enabled Excel file). I don’t know what zip file you’re talking about.

Jun says:

May 5, 2010 at 7:38 am

Hi, just learning how to use VBA… How do I calculate “depth” if instead of indenting the subtasks as you described, I put each subtask into a new column? Any advice would be appreciated. Thank you!

May 5, 2010 at 12:50 pm

Jun – I don’t have a full solution for you, but the following code should return the last used or “deepest” column in a given row. Replace the code on line 40 with this:

depth = Cells(r, 2).End(xlToRight).Column

This should return depth as a number corresponding to the last used column (A=1, B=2, etc). It’s going to consider *any* data in a column, so if there are static columns beyond the task descriptions (dates, resource names, etc), you’ll need to subtract those from “depth.”

Be careful with this method. Unless you put your task descriptions in the right-most column you could end up limiting your available subtask depth.

May 5, 2010 at 1:40 pm

I naively tried just changing line 40 with what you provided for me. It runs into an infinite loop. No clue why… (I did try using your code using indents for subtasks and it totally works! Great code!!) Do think you help me come up with what I am missing? Thank you.

May 5, 2010 at 2:15 pm

Sorry, it is not an infinite loop. When I stepped through, the new line 40 gave me depth=16,000.

Eve says:

May 17, 2010 at 4:22 am

Hello Jermey….
This is great 🙂 yet when i tried it the numbering started not from the fisrt row that has my data yet it started with the below row, i.e. not row number 2 but 3. in addition the nubering starts with 0.1 not 1!!!! thats really odd! thought itried it to your example and it works just fine!!!

On the other hand, i type my data, the main one, on a cell in one column, and the sub ones on a different column, lets say: Main points are located in col. B and the sub are located on col. C… etc for other sub points. so no indentation happens.

So actually what I thought of is, since I enter the data in this way; I’m trying to do whenever there is a non blank data in a certain cell so this is a main point and shall take the first level of numbering (i.e. 1), while if its blank then those belong to the data that are supposedly sub points of the main one (1.1, 1.2, 1.3… etc, 2.1, 2.2,… etc).

I know I have to increase the numbering of the sub as well as the main one whenever a nonblank data occurs. Yet I cant do it in the simple if function, nor working for me when I VB script it! not to mention I’m not that good in scripting or VB!

I really have to get this done but I need a help from you, after all, you seem to be very expert in this 😀

Please advise and help me out.

I’ll be waiting for your assistance, and if possible your solution.

Many thanks in advance and wish you a joyful day.

Regards,
Eve

sandie says:

June 10, 2010 at 5:45 pm

thanks for this, the company i work for wont invest in Project, so this is the next best thing, cheers

Jared says:

July 6, 2010 at 3:40 pm

Great code!

What would it take to be able to shift the cells down (insert a row above) without having the numbering column header become numbered.

Renumbering makes the 1st entry be “2”.

Thanks

July 6, 2010 at 3:41 pm

Also,

The format forces some unwanted “bolds”. What would it take to remove these formatting changes?

September 23, 2010 at 12:34 pm

Great Macro indeed. I’m looking for a similar macro which, instead of creating numbering, automatically creates row based groups.

Any ideas are greatly appreciated.

Chris

Jeff says:

October 15, 2010 at 4:26 pm

Hi Jeremy,

Just curios if this code can be adapted to add a “.” to the end of the numbers?

1.
1.1.
1.2.
etc.,

October 15, 2010 at 10:48 pm

Hey y’all. Sorry for the delayed response. Life is busy again! Here are some completely untested and probably wrong answers …

Eve & Jared: In the linked sample spreadsheet, line 27 should say “r = 3”. That is the starting row. If you want it to start numbering at a different row, change the “3” to whatever row ought to contain WBS 1.0.

Also Jared: Again on the linked sample, bolding is happening on lines 98-108. You can safely remove / comment out the lines that say “Cells(r,#).Font.Bold = True”.

Chris: Not sure what you’re looking for. You can explain further here if you want, although you might have more luck searching / asking somewhere like http://www.ozgrid.com/forum/. They are much smarter over there than I am.

Jeff: Sure. A safe way to append a period after the FULL wbs name is assigned is to insert a line right before the wbs string is written to the cell. So, look for line 89 which should be a comment that says “Populate target cell with WBS number” and right after that comment (i.e., right BEFORE the line that says “Cells(r,1).Value = wbs”) insert a line that says: wbs = wbs & “.”

Good luck & happy coding to all.

Justice Steele says:

October 29, 2010 at 8:48 am

Hello!
I love your tool and I’ve had it in my toolbox since last year. I revisited it yesterday and ran into a problem. Renumbering causes Excel to freeze, go into “Not Responding” mode, and no renumbering occurs. I’m running 2007 but it looks like you’ve tested it already. I enable macros . . . and I’m not sure what to do next. I tried to download the tool again but when I look in the zip file I do not see the Excel file.
Any suggestions? I’m not a developer so my ability to understand and tweak the code is limited, although I follow instructions well.
Thanks,
Justice

October 29, 2010 at 9:19 am

Justice – you’re the second person to reference a Zip file, but I don’t know what you’re referring to. The only attachment I have posted related to this code is the sample spreadsheet in .XLSM (Excel 2007 Macro-Enabled) format: http://j.modjeska.us/data/wbs_numbering.xlsm. Maybe someone else is redistributing this code in another format? If so, I obviously can’t vouch for anyone else’s code or files.

The “not responding” error is most likely due to Excel being in an infinite loop. That may mean that you’ve accidentally deleted the “END OF PROJECT” text from column B. If you don’t like “END OF PROJECT” you can change it to something else (see lines 30-31 in code sample above), but you need to have some kind of flag at the end of column B so the code knows when to stop. Hope that helps. If it’s something else, let me know, or send me your spreadsheet and I’ll take a look.

November 1, 2010 at 11:15 am

Thank you!
I deleted End of Project.
It’s so weird – when I click on your link it still gives a zip folder. The name of the folder is wbs_numbering.zip.
I can download the folder and email it to you for inspection if you’d like.
Thanks,
Justice

November 1, 2010 at 11:39 am

Justice — my apologies. Apparently MS Office files randomly turning into Zip files is a known issue (http://www.webdeveloper.com/forum/showthread.php?threadid=162526). Unfortunately I have no way to know if I’ve fixed it since I am unable to replicate the problem on any of my computers or any of my friends’ computers, so maybe just try changing the extension to “.xlsm”? Alternatively, here is an actual Zip file — maybe this will work properly: http://j.modjeska.us/data/wbs_numbering.xlsm.zip.

November 5, 2010 at 1:29 pm

Thank you – the actual Zip file works perfectly.

George says:

November 18, 2010 at 7:03 am

What can I say… Thank you, Thank you, Thank you, for creating and sharing this Excel code to generate WBS numbering.

Don Francis says:

March 15, 2011 at 7:38 am

This is great code. I found it as I was looking for the opposite outcome. This I will use, but can you tell me if you have come acrossed any code that will:

Take a WBS currently with a numbering scheme and a flat tasks list and index the tasks based on the WBS number. Example: 3.4 will indent 1 tab; 3.4.5 will indent 2 tabs, etc?

I can get a WBS out of Project for Excel, but it does not come acrossed with the indenting, however the WBS numbers come acrossed in their own cell.

Any help will be appreciated.

Thanks,

Don

Patch says:

March 21, 2011 at 8:57 am

Hi there
As a non-VBA expert this seems to me to be an impressive bit of code, but almost too complicated for what I need. How easy would it be to have something similar, but instead of the outline level being determined by the level of indentation, for it to be determined by an index number in another column?

The reason for this is that I already have a long list of tasks and I don’t want to have to manually set the indentation level for each of them.
Instead I would like to insert a new column as column A (‘Outline Level’) and be able to put numbers in there to determine the outline level for each row (i.e. 1=X; 2 = X.X; 3 = X.X.X, etc).
I’m sort of assuming that would be quite an easy change but have no idea how to go about it myself… any suggestions?

Many thanks.

Patch

Cristián says:

May 1, 2011 at 4:11 pm

Hi Jeremy

Excellent Job!
It´s very useful for WBS and for accounting design too.
I would like to know if you have developed another Excell Macro for Project Management.
Thank you so much for sharing.

Cristián
Buenos Aires – Argentina

Jarps says:

July 29, 2011 at 8:41 am

Hi Jeremy
A few months ago, I have created a gantt template in excel for a specific project. In spite of using MSProject more frequently in my work, I needed in that specific project to compile in a single file several types of data, including a sort of project management. Your code of wbs was a good increment to my template. Thank you very much for that. Now if you or anyone else want to see a simplified example of that please visit this link http://exceleoutrascoisas.blogspot.com/2011/07/excel-gantt-template.html and send me your feedback.

Jarps
Porto, Portugal

Paddy says:

April 9, 2013 at 10:44 am

just wanted to let you know that your script is still being looked after!
I altered the script a little bit and stored both the wbs input and output in arrays, in order to populate a userform with the results.

Originally I was looking for a script that could help me build a tree à la Windows Explorer (the one that sows up when clicking the + next to a folder), but the wbs code is much clearer and certainly more professional.

Thanks again
Paddy

April 9, 2013 at 11:06 am

Outstanding. Thanks Paddy! So glad this is still useful.

Ryan says:

May 23, 2013 at 7:22 am

Thanks much for posting this code…I just used it as the basis for an excel file that is the source document for a “self-building” power point presentation. This gave me the start I needed in excel to order tasks and subtasks that could then be turned into slides and subslides in ppt. Thanks!

-Ryan

Kristian says:

July 12, 2013 at 7:52 am

Many thanks for posting this. Exactly what I was looking for.
Really makes my project plan easier to read (and maintain).

(I changed basenum to start at 1.)

Now to get spaceship supplies …

-Kristian

Ava says:

October 9, 2013 at 6:09 am

Thank you! Exactly what I was looking for for easily generating a proposal outline template. Microsoft should probably hire you to write this into excel’s coding, I can’t believe excel doesn’t come with this functionality out of the box. Again, THANK YOU, you’ve helped me and a lot of other people look good 😉

Manochehr says:

January 6, 2014 at 6:53 pm

That is great Jeremy,

Is it possible also to write a function to outline the list based on WBS numbering?
Can anyone guide me how to it also.

Manochehr

January 6, 2014 at 7:13 pm

Another question is that: Is it possible to auto grouping the list based on WBS numbering in excel?

Steve says:

January 15, 2014 at 7:52 am

Thanks for the doing this! I can’t seem to use the macro on more than one sheet in a workbook. Is there something I need to do to get that to work? Thanks again!

Brian Layzell says:

February 10, 2014 at 12:09 pm

Another thanks! Works like a charm and it saved me a few hours of time. I’m using it directly in Excel to create a project schedule worksheet with a Gantt chart (stacked column chart). The y-axis will contain the task name, now with the WBS number concatenated to it (in parenthesis).

Jamie says:

May 7, 2014 at 7:22 am

I’d like to be able to have tasks with 1 indent (1.1, 1.2, etc) bolded. Right now the master tasks with a 0 indent bold, and any tasks that have sub-tasks will bold as well. I have tried a few things but can’t figure it out (I’m a novice at best). Would you mind providing me with the script to do this? Thank you!

Ricky Bell says:

June 11, 2014 at 10:37 am

Works great! I added to it if anyone is interested. Mine is based off the user’s selection (As long as they select 2 columns). It uses your numbering code then I added code to group the rows and use the built in + and – excel uses for grouping.

http://www.rickybell.com/temp/excelgrouping.zip

Also, mine ignores the zero indent items (client request).

Thanks again for the help!

Tami D says:

June 16, 2014 at 11:35 am

Jeremy,
I would really like to use your code. I copied it into my workbook VBA module but when I run it on Excel 2010 I keep getting the message that Excel workbook is not responding. Any suggestions?
Tami

Jeremy says:

July 25, 2014 at 12:14 pm

Hi Tami. Can you open and run the sample spreadsheet I linked in the article?

David says:

July 31, 2014 at 2:21 pm

Hi Jeremy,
This really is great. I was wondering if it would be possible to key off of column to control the numbering level. In my case, I am attempting to create a Test Plan in Excel which consists of Test Cases which would correspond with the second level of the spreadsheet numbering (i.e., 1.1), and Test Steps which would correspond with the third level of the spreadsheet numbering (i.e., 1.1.1). Basically, I am trying to create a one to many relationship between the Test Case and the Test Steps and have them numbered accordingly. But I would like to have the Test Case in one Column and the Steps in another column, so the indentation doesn’t quite work for that. Any insight you could provide would be greatly appreciated.
Thanks,
David

Matt Hellyer says:

August 19, 2014 at 11:45 pm

Thanks for this VBA code! It was really helpful for me in generating hierarchical step numbers in a procedure that’s constantly changing – a similar application to numbering the tasks in a WBS. In the course of using your code, I made some changes to make it simpler, more generic and more configurable.

If you would like to see what I did, let me know the best way to send it to you. If you like it, I’m happy for you to publish it here too.

Cheers,

Matt

Jeremy says:

October 24, 2014 at 5:51 pm

Hey Matt. Feel free to email me an Excel spreadsheet with the changes and I’ll be happy to post it here. Email is blog@daqron.com. Thanks!

kgs-ks says:

November 7, 2014 at 8:19 am

Hey all beeing interested in WBS-like hierarchies

i found this solution published above very intresting as i searched a solution for longer time.
But now I got my own solution for WBS structure not only for “viewing” but instead really using as structuring / sorting-key with all actions for manipulation supported

That is: it works right opposite not structuring by indent level but numbering, sorting. inserting, levelUp/levelDown a.s.o and doing formating afterwords
.
Anyone else know such a solution too?

Karl

Jessica says:

November 11, 2014 at 6:37 pm

Hi,
Wondering if there is a way to use this method which will allow filtering?
I apologize, but I am not very familiar with VBA so there may be a simple answer.

Would like to filter to the Major Tasks, and/or show only the info associated with a specific hierarchy – level 4 (including 4.1, 4.1.1, 4.2 etc.)
Thanks,
Jessica

Louise says:

March 2, 2015 at 7:34 am

Hi Matt, Jeremy,

Would you be willing to share the new version? I have been using the original version which works quite well.

Merci!

Louise

Sharon Shoptaw says:

April 9, 2015 at 1:00 pm

I am using this for process flows. I would like to add tabs and have different process flows in each tab. When I do this, the control button does not work. It seems to be linked to Sheet 1. Can you help me to adapt this workbook to meet my needs?

Ronil says:

June 9, 2015 at 11:48 am

Hi,

When I change the tasks to hyperlinks, the wbs numbering gets messed up.

For example if my WBS before adding the hyperlinking is as below

1. Build a Spaceship
2. Fly around in Spaceship

If I add a hyperlink to ‘Fly around in Spaceship’ then the numbering changes to as the one below

1. Build a Spaceship
1. Fly around in Spaceship

Both of them get coded as 1

Ketan says:

July 23, 2015 at 2:21 am

Hi, Jeremy,

I have used this macro and modified a bit as per my requirement. in my field of work we usually use 001.001.001 format, so i have modified below command in the code:

For aloop = 0 To depth
wbs = wbs & “.00” & CStr(wbsarray(aloop))
Next aloop

But then when the number reaches double digit it looks like 001.0012.0015 but what I am looking for is 001.012.015 so can you help me in modifying this code.

Regards
Ketan

russ says:

August 31, 2015 at 8:51 am

Hello Jeremy, I love this spreadsheet and it is exactly what I was looking for. Question is though when I make a copy of the tab (in the same workbook) and move it to the End the code does not work when I enter the data in the new spreadsheet. I did not see anything in the macro that would prevent a new copy of the tab from working however I am not the guru so though I would ask. What happens is the auto numbering doe not work after making a copy of the sheet.

Flavio says:

January 5, 2016 at 1:14 pm

Thanks. Really helped! Easy to adjust and perfectly functioning.

AnonJP says:

April 14, 2016 at 5:40 pm

Thank you!!!!!!!

Very well written and clean macro.

chris may says:

February 10, 2017 at 2:28 am

Hi there !

Just added a few lines ..
– starts @ any row or colum, (just select starting cell “code/id number”, could be replace by a FIND function)
– macros goes down until it finds “EOF” in “task name” column .. or stops after 5 blank lines (just in case you forget typing EOF.
– added level one color

He it goes

Sub WBSNumbering()

‘From http://j.modjeska.us/?p=31
‘Renumber tasks on a project plan
‘Associate this code with a button or other control on your spreadsheet

‘Layout Assumptions:
‘Row 1 contains column headings
‘Column A contains WBS numbers
‘Column B contains Task description, with appropriate indentation
‘Some text (here we assume “END OF PROJECT”) delimits the end of the task list

‘Hide page breaks and disable screen updating (speeds up processing)
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
‘Format WBS column as text (so zeros are not truncated)

Dim r As Long ‘Row counter
Dim depth As Long ‘How many “decimal” places for each task
Dim wbsarray() As Long ‘Master array holds counters for each WBS level
Dim basenum As Long ‘Whole number sequencing variable
Dim WBS As String ‘The WBS string for each task
Dim aloop As Long ‘General purpose For/Next loop counter

curcol = ActiveCell.Column
r = ActiveCell.Row + 1 ‘Starting row
bgcolor = 24 ‘ purple

‘ActiveSheet.Range(curcol).NumberFormat = “@”

basenum = 0 ‘Initialize whole numbers
ReDim wbsarray(0 To 0) As Long ‘Initialize WBS ennumeration array

‘Loop through cells with project tasks and generate WBS
Do While Cells(r, curcol + 1) “EOF”
‘ count blanks
If Cells(r, curcol + 1).Value = “” Then blank = blank + 1
If blank > 5 Then
‘if more than 5 lank lines then surely reached enf of projet
Exit Do
End If
‘Ignore empty tasks in column B
If Cells(r, curcol + 1) “” Then

‘Skip hidden rows
If Rows(r).EntireRow.Hidden = False Then

‘Get indentation level of task in col B
depth = Cells(r, curcol + 1).IndentLevel

‘Case if no depth (whole number master task)
If depth = 0 Then

‘increment WBS base number
basenum = basenum + 1
WBS = CStr(basenum)
ReDim wbsarray(0 To 0)

‘Case if task has WBS depth (is a subtask, sub-subtask, etc.)
Else

‘Resize the WBS array according to current depth
ReDim Preserve wbsarray(0 To depth) As Long

‘Repurpose depth to refer to array size; arrays start at 0
depth = depth – 1

‘Case if this is the first subtask
If wbsarray(depth) 0 Then

‘Case if we are incrementing a subtask
Else

‘Only ennumerate WBS as deep as the indentation calls for;
‘so we clear previous stored values for deeper levels
If wbsarray(depth + 1) 0 Then
For aloop = depth + 1 To UBound(wbsarray)
wbsarray(aloop) = 0
Next aloop
End If

‘Assign contents of array to WBS string
WBS = CStr(basenum)

For aloop = 0 To depth
WBS = WBS & “.” & CStr(wbsarray(aloop))
Next aloop

‘ set text format
Cells(r, curcol).NumberFormat = “@”
‘Populate target cell with WBS number
Cells(r, curcol).Value = WBS

‘Get rid of annoying “number stored as text” error
Cells(r, curcol).Errors(xlNumberAsText).Ignore = True

‘Apply text format: next row is deeper than current
If Cells(r + 1, curcol + 1).IndentLevel > Cells(r, curcol + 1).IndentLevel Then

Cells(r, curcol).Font.Bold = True
Cells(r, curcol + 1).Font.Bold = True
‘Else (next row is same/shallower than current) no format
Cells(r, curcol).Interior.ColorIndex = 0
Cells(r, curcol + 1).Interior.ColorIndex = 0
Else
Cells(r, curcol).Font.Bold = False
Cells(r, curcol + 1).Font.Bold = False
Cells(r, curcol).Interior.ColorIndex = 0
Cells(r, curcol + 1).Interior.ColorIndex = 0
End If
‘Special formatting for master (whole number) tasks)
If Cells(r, curcol + 1).IndentLevel = 0 Then
Cells(r, curcol).Font.Bold = True
Cells(r, curcol + 1).Font.Bold = True
‘Add whatever other formatting you want here
Cells(r, curcol).Interior.ColorIndex = 24
Cells(r, curcol + 1).Interior.ColorIndex = 24
End If

‘Go to the next row
r = r + 1

Roger says:

March 7, 2017 at 6:53 am

Jeremy: I downloaded your WBS with the VBA macro. It was exactly what I was looking for…. until after about 15 minutes into using it. Suddenly the workbook (and Excel) locked up after clicking on “Renumber Tasks!” I had to force quit (OSX) Excel. I then tried editing several more times, but again each time I clicked on “Renumber Tasks!” everything freezes.

Any thoughts?

Thanks in advance, Roger

Lou says:

March 19, 2017 at 3:10 pm

Hi, I tried to use your auto numbering macro . It works good, but ctrl+z is not working after I run the macro. Is it possible do fix it somehow?

Thank you .

James D says:

May 20, 2017 at 1:37 am

Awesome, thanks heaps!

Shannon says:

June 7, 2017 at 12:16 pm

Hi there,

Really helpful code! I have one quick question. My code is starting with 0.1, however, I would like the first WBS number to be 1 and then continue on. Is there a way I can make that happen?

Let me know!

nick says:

February 27, 2018 at 8:33 pm

can this be implemented via a vba formula instead of a sub? thanks

snb says:

April 16, 2018 at 10:00 am

If the indentation in column B is defined by number of spaces, this code suffices:

[code]Sub M_snb()
sn = Split(Replace(Space(20), ” “, “0 “))
n = 0

sp = Cells(1).CurrentRegion

For j = 2 To UBound(sp)
y = Len(sp(j, 2)) – Len(Trim(sp(j, 2)))
If y = 0 Then
sq = sn
sq(0) = n
End If
sq(y) = sq(y) + 1
n = sq(0)
For jj = 0 To y
sp(j, 1) = sp(j, 1) & sq(jj) & “.”
Next
Next

Cells(1).CurrentRegion = sp
End Sub[/code]

Luke Tarrant says:

December 6, 2018 at 2:45 pm

Thank you so much for this tool. I have been looking for something to do this for some time and have finally found one. Props to you sir!

Michael Wray says:

April 10, 2019 at 12:59 pm

Here’s a quick an manual formula that I ended up using. You just have to start your first number with a manual entry:

For example my WBS #s are in column B, so here are my cell values to get this started
B1: Task #
B2: 1
B3: = 1.1
B4:=LEFT(B3,1)&”.”&(RIGHT(B3,LEN(B3)-FIND(“.”,B3,LEN(B3)-2))+1) (shows 1.2)
B5: =LEFT(B4,1)&”.”&(RIGHT(B4,LEN(B4)-FIND(“.”,B4,LEN(B4)-2))+1) (shows 1.3)

..this will work up to 99. If you need more than this, then change the last LEN()-2 to LEN()-3…

B103: =LEFT(B102,1)&”.”&(RIGHT(B102,LEN(B12)-FIND(“.”,B102,LEN(B12)-3))+1) (shows 1.1.101)

To create a 2nd level format like 2.1.1, adjust the first left formula to give you the digits up to the 2nd decimal, which woudl be left(B3, 3)….

B2: 1
B3: =1.1.1
B4:=LEFT(B3,3)&”.”&(RIGHT(B3,LEN(B3)-FIND(“.”,B3,LEN(B3)-2))+1) (shows 1.1.2)
B5: =LEFT(B4,3)&”.”&(RIGHT(B4,LEN(B4)-FIND(“.”,B4,LEN(B4)-2))+1) (shows 1.1.3)

Jorge M says:

August 27, 2019 at 10:26 am

Hello,

Fantastic vba code. Been looking for something like this for ages. I work in budget of civil construction, so wba is like the base of any table or gant chart. Thanks a lot for put this online this way.
Best regards to you.

August 27, 2019 at 11:19 am

Sorry to bother, but i have a question is there a way to make the code ignore some lines if for instant it start with #. I notice that your code ignores empty lines but besides this i need to ignore lines with notes or comments that i put on budget. Sometimes a long the table it’s required to put comments like type of material considered, number of hours of works, etc., it can happen several times.
I don’t enough of vba code to make the change myself.

JorgeM (from Portugal)

Mike Williams says:

August 13, 2020 at 1:16 pm

This worked like a charm. I work in project management, and it has saved me a ton of time. My co-workers are very jealous. Thank you for sharing.

Mike

Paul says:

October 22, 2020 at 1:50 pm

Hello!
I have two different lists that are separated by an empty row. Each list ends as “END OF PROJECT”
How adapt the macro for that? I tried to replace “do while” with “for” but it didn’t work.

José João Santos says:

December 11, 2020 at 3:02 pm

Jeremy!

That code is what i was looking for my construction measurement files, but I wonder if you, or someone here, can help me with something.

I need to be able to have some lines without numbering (for notes, etc) so if would be great if the code would ignore the cells, on the first column, that have “MD” written on them. Also if would work if the code ignored cells with no indentation for example!

Pleaseee! =)

Need code to change row that is formatted as text to be number with no decimals says:

March 4, 2016 at 5:17 am

[…] seem to work. In my worksheet I also have a macro, the code is below: Sub WBSNumbering() 'From Project-Style (WBS) Numbering in MS Excel 'Renumber tasks on a project plan 'Associate this code with a button or other control on your […]

Project-Style (WBS) Numbering in MS Excel

Project-Style (WBS) Numbering in MS Excel

Notes

VBA Code for WBS numbering in Excel

78 Comments » for Project-Style (WBS) Numbering in MS Excel

1 Pings/Trackbacks for "Project-Style (WBS) Numbering in MS Excel"

Leave a Reply Cancel reply