• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro/VBA for grouping/outline of WBS structure

Status
Not open for further replies.
Sandy_X said:
ok, what i found was, you only have to change into:
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Integer) As Long
- if anyone of you is using the 64 bit Office version. Please note that, to use the Stoplight WBS Tree, the pictures you use should be in the same directory nas the workbook is, because in any other directory it will not update.
Click to expand...
sheet "WBS PIC" does not work.check that too.
 
I am sorry, i am using Office 32 bit on my own and have no clue what to change inside the codelines to make it executable in Office 64 bit.
 
Dear Sandy

Thanks for your explanation, but I'm running 64-bit Windows and now I have stopped and can not run your macro. Please help me if possible
 
Luke M said:
Ah. Okay, tried to do a bit more testing. This seems to be an improvement, at least.
Code: 
Sub BuildOutline()
Dim lastRow As Long
Dim grpRow(1 To 8) As Long
Dim i As Long, j As Long
Dim curLVL As Long

Application.ScreenUpdating = False
With ActiveSheet
    .Cells.EntireRow.ClearOutline
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Setup initial values
   For i = 1 To 8
        grpRow(i) = lastRow
    Next i

    For i = lastRow - 1 To 2 Step -1
        curLVL = lvlCount(.Cells(i, 1).Value)
        If curLVL > lvlCount(.Cells(i + 1, 1).Value) Then
            grpRow(curLVL) = i
        ElseIf curLVL < lvlCount(.Cells(i + 1, 1).Value) Then
            .Range(i + 1 & ":" & grpRow(curLVL + 1)).EntireRow.Group
            'If we go up a level, all previous lower levels reset
            For j = curLVL + 1 To 8
                grpRow(j) = i - 1
            Next j
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

Function lvlCount(WBS As String) As Integer
lvlCount = Len(WBS) - Len(Replace(WBS, ".", "")) + 1
End Function
Click to expand...
Hi Dear
I used of your macro for grouping my Project WBS but unfortunately I have error in macro or I have Debug. I give snapshot of my PC screen for clarified this problem. please find attached file for your Macro Snapshot.
Best Regards,
 

Attachments

  • Macro Error.jpg
    Macro Error.jpg
    302 KB · Views: 11
Hi, Sorry to bring this again. Can someone point where i can find the link/ step by step instruction to run the Better Outliner.xlsm to achieve the formatting and outlining
 
To install a macro:

1. Right-click on a sheet tab, and select 'View Code'
2. In the Visual Basic Explorer (VBE) that opens, go to Insert - Module
3. Paste the given code into the white space that appears
4. Close the VBE
5. In your workbook, open the macro menu (Alt+F8)
6. Select desired macro and hit 'Run'
 
momcaro said:
Beautiful!!! I cannot thank you enough, there is no way I could have come up with any of that and this has been driving me crazy... so THANK YOU!!
Click to expand...
Luke M said:
Ah, I see now. Goofy little thing about XL...originally accountant type people designed it, and their totals are at the bottom, so the outline rolls down. In Data ribbon, in bottom of Outline group, you can hit the little button, and remove that checkmark:
View attachment 15046

However, since we may use this on other workbooks, I added a line into the code to have the macro make this switch. :cool:
Click to expand...
Hi, I did used it earlier, but now i dont get to see how i can use on the excel output of project file. Can i request your support to explain - step by step on how to use this better outliner.xlsm file and acheive the required formating.
 
Luke M said:
To install a macro:

1. Right-click on a sheet tab, and select 'View Code'
2. In the Visual Basic Explorer (VBE) that opens, go to Insert - Module
3. Paste the given code into the white space that appears
4. Close the VBE
5. In your workbook, open the macro menu (Alt+F8)
6. Select desired macro and hit 'Run'
Click to expand...
Thanks for that Luke, Yes In fact, there were 2 modules which I had copied and even saved the file as macro enabled, but still the formatting is not happening. not sure why it is so. What can I do next, thanks for your support in advance.
 
hi i have a question if in a wbs in last row you rught 1.4 or 1.5 it can not assign true group level ..why ?
 
mojtaba
This thread has opened few years ago.
You should open a new thread.
Please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
There are more hints - how You should continue.
 
I also indent my cells based off of WBS numbers but I use a count of the periods. I create a separate column which counts the number of periods within each WBS element for each row. The number of periods indicates the level of the WBS element. I then run a small macro which indents the cells based off of the period count.
 
gulsimsur said:
I also indent my cells based off of WBS numbers but I use a count of the periods. I create a separate column which counts the number of periods within each WBS element for each row. The number of periods indicates the level of the WBS element. I then run a small macro which indents the cells based off of the period count.
Click to expand...
The issue with grouping is it difficult to determine how to group within a group based off of the WBS structure, considering there could be several rows which need grouped within another group. If I could somehow capture the first and last cells of the first highest WBS element and then make my way inward.... dickssportinggoods feedback www.lowes.com survey
 
Status
Not open for further replies.
Back
Top