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. 1.

    Here is a sample spreadsheet, complete with the code and a command button, for those who want something ready-to-use.

  2. 2.

    Tested in Excel 2003 and 2007 for Windows.

  3. 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. 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. 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


78 Comments » for Project-Style (WBS) Numbering in MS Excel
  1. Nick Leeder says:

    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?

  2. Jeremy says:

    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.

  3. Dan says:

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

  4. Scott says:

    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.

  5. Brent says:

    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.

  6. Jeremy says:

    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.

  7. Mark says:

    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

  8. Chris says:

    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

  9. Martin says:

    AWESOME!!! Thank you very much.

  10. Ellerd says:

    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?

  11. Jeremy says:

    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.

  12. Guy says:

    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

  13. Tony Wright says:

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

    Tony

  14. Etienne says:

    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

  15. Jeremy says:

    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.

  16. Tom says:

    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.

  17. Jeremy says:

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

  18. Jun says:

    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!

  19. Jeremy says:

    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.

  20. Jun says:

    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.

  21. Jun says:

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

  22. Eve says:

    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

  23. sandie says:

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

  24. Jared says:

    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

  25. Jared says:

    Also,

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

    Thanks

  26. Chris says:

    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

  27. Jeff says:

    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.,

  28. Jeremy says:

    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.

  29. Justice Steele says:

    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

  30. Jeremy says:

    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.

  31. Justice Steele says:

    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

  32. Jeremy says:

    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.

  33. Justice Steele says:

    Thank you – the actual Zip file works perfectly.

  34. George says:

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

  35. Don Francis says:

    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

  36. Patch says:

    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

  37. Cristián says:

    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

  38. Jarps says:

    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

  39. Paddy says:

    Hi Jeremy,

    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

  40. Jeremy says:

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

  41. Ryan says:

    Jeremy,

    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

  42. Kristian says:

    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

  43. Ava says:

    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 😉

  44. Manochehr says:

    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

  45. Manochehr says:

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

  46. Steve says:

    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!

  47. 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).

  48. Jamie says:

    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!

  49. Ricky Bell says:

    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!

  50. Tami D says:

    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

  51. David says:

    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

  52. Matt Hellyer says:

    Hi Jeremy,

    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

  53. kgs-ks says:

    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

  54. Jessica says:

    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

  55. Louise says:

    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

  56. Sharon Shoptaw says:

    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?

  57. Ronil says:

    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

  58. Ketan says:

    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

  59. russ says:

    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.

  60. Flavio says:

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

  61. AnonJP says:

    Thank you!!!!!!!

    Very well written and clean macro.

  62. chris may says:

    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

    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)

    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

    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

    ‘ 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

    End If

    End If

    ‘Go to the next row
    r = r + 1

    Loop

    End Sub

  63. Roger says:

    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

  64. Lou says:

    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 .

  65. James D says:

    Awesome, thanks heaps!

  66. Shannon says:

    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!

  67. nick says:

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

  68. snb says:

    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]

  69. Luke Tarrant says:

    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!

  70. Michael Wray says:

    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)

  71. Jorge M says:

    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.

  72. Jorge M says:

    Hello,

    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)

  73. Mike Williams says:

    Jeremy,

    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

  74. Paul says:

    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.

  75. José João Santos says:

    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! =)

1 Pings/Trackbacks for "Project-Style (WBS) Numbering in MS Excel"
  1. […] 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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.