10
Okay, I think this one is a really hard one for yous geniuses out there. I'm going to try my best to explain in as much detail as I can. And for the most part I just want to know if I'm doing this the right way of it there's an easier way to do it.
Background/Platform:
Windows XP
Office Excel 2007
Visual Studio Tools for Office 2008
Document level customization
VB.Net
I have a spreadsheet that is used to project which site(s) a person will be assigned to for the next six months. There are a number of Field Managers(4) each of whom are in charge of a number of Programs (not computer programs). Each Program has a variety of work locations and at each work location there is a number of Field Representatives.
A Field Manager may like to group their sites by type, for example there might be three Air National Guard sites that belong to the Hippo program. Or a Field Manager might want to group their sites by location; such as CONUS (Continental United States) or OCONUS. A site may in theory belong to more than one group. For example, Air National Guard site X could also be OCONUS.
So as we can see there's a hierarchy here that looks something like:
Field Manager/Program/Location (conus or Oconus)/site group/site
The spreadsheet looks something like this:
Field Manager 1
Hippo Program
OCONUS
OCONUS Site 1
Employee 1
Employee 2
Employee 3
OCONUS Site 2
Employee 4
Employee 5
OCONUS Site 3
Employee 6
CONUS
ANG Sites
ANG Site 1
Employee 7
Employee 8
ANG Site 2
Employee 9
CONUS Site 1
Employee 10
Employee 11
Elephant Program
More Site Groups with Sites and Employees
Field Manager 2
Organized much the same as Field Manager 1
Now, what I've done is I've added a treeview control with checkboxes to a custom actions pane. I would like to assign each treeview node to a given site and when I uncheck a Treeview node it'll hide the rows that are associated with that node. However, if I uncheck a Site Group, I want it to uncheck all childnodes below it and hide all the rows in the spreadsheet that make up that site group. For example, say I want to filter out OCONUS Site 1 then I'll just uncheck it's checkbox in the treeview and it'll go through the spreadsheet and hide OCONUS Site 1 and the employees underneath it.
So I'm thinking that for each row in the spreadsheet, I could have a column along the left hand side that includes a treeview path. And then use the Range.Autofilter method to search through the column finding rows that contain Program Name, or Site Group Name, or Site.
What I had done earlier was created five columns to the left of my spreadsheet and named each one "Manager", "Program", "Location", "Site Group", and "Site" accordingly. And then filled in the info to identify each row. But ran into problems.
Anyway, is does anyone know of a way to get a treeview node to figure out on it's own which rows to hide in an excel spreadsheet? Could I have possibly written this post with more complexity? And it probably sounds like I'm just asking someone else to do it for me, but I hope not. Thanks anyway for anyone who can give me a better idea of how to do this.
Would databinding my treeview to my spreadsheet do what I'm talking about?
