How to automate setting up groups for expanding and collapsing rows

[lrutt]

I have a routine that imports a Microsoft project plan. That all works perfectly.

I'd like to create some VBA code that would apply the "+" and "-" to collapse and expand groups based on the WBS code.

I'd like a "+" by the 1, 1.1, 1.1.1, 1.2, 1.3 and so on depending on the indent level when imported. That way I can hide sections that either are complete in the project or that may not be in play yet. I think it could/would be dependent on possibly counting the periods "." in the string in the WBS column but can't wrap my head around doing that with the nested situation this presents. It's possible there could be 7 or more levels.

Any help would be appreciated.

Those WBS codes look as follows for example:

[TABLE="width: 132"]
<colgroup><col width="176" style="width: 132pt; mso-width-source: userset; mso-width-alt: 6257;"> <tbody>[TR]
[TD="class: xl67, width: 176, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 176, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 176, bgcolor: white"]1.1[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.1.1[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.1.2[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.1.3[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.1.4[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.1.5[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.1.6[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 176, bgcolor: white"]1.2[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.2.1[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.2.2[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.2.3[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 176, bgcolor: white"]1.3[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 176, bgcolor: white"]1.3.1[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

you could use a userform with a treeview control. Something along these lines:

https://www.homeandlearn.org/introduction_to_treeviews.html

 

[lrutt]

As a project plan can be several hundred lines long, I need to keep this in the worksheet. I apply some conditional formatting etc. as the lines are imported to facilitate readability. I would also need to be able to create a .pdf of the worksheet with groups collapsed etc.

If there are options to do this within the worksheet, that is what I need.

Thanks

 

[VBA Geek]

if you structure your table like below then with a pivot table you can do this

 

[lrutt]

Unfortunately the MS Project import routine takes the WBS straight from the project plan and copies it into a column. I need to try and work with the single column as shown.