• 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

shahsavand

New Member
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.
sheet "WBS PIC" does not work.check that too.
 

Sandy_X

New Member
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
 
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
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

srimahesh1

New Member
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
 

Luke M

Excel Ninja
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'
 

srimahesh1

New Member
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!!
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:
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.
 

srimahesh1

New Member
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'
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.
 
Top