hierarchys - can you build an elegant hierarchy using vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi all

a question to all you experts - is it possible to build a basic hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a drill up
and drill down function usinf standard excel

if you reply - thanks in advance
 
i should explain that my lsit is simple in nature

lets say


4 mm bar
8 mm bar
12 mm bar

all reporting up to bars which reports up to Total Steel

i.e a classic OLAP hierarcy ( sort of analysis server territory )

how can we do this in excel
 
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing this
naturally ) but i suspect i need an OCX control to help me - it must have
been done before
 
Christopher,

What does this translate to in terms of Excel ? I understand the concept
but what should the resulting excel file be able to *do* ? Is it a tool for
creating the relationships, displaying the relationships, or what ?

Tim.
 
Dear Tim

sorry if i confussed the issue - all i want is to have a list of product
codes
in a data range lets say B10:B59 and be able to say this code reports to
Bars or Plates - i.e build a parent child relationship of course many levels
deep so i need my hierarchy to have a level number , a parent or child
relationship , a description - thus at any stage i can drill up or down on
any given member in a list box for example

i guess im trying to find an OCX or another item to add to the VBA toolkit

chris
 
i should also add if I have a hierarchy on products i need code to add or
delete or move any given item in the hierarchy - this functionality will be
added to a system i have written in Excel 2003 and needs to be user friendly
- so far the systems works but i see no easy way for hierarchy generation
over many dimensions even if they only have single hierarchys - pivot table
does not help me - i need raw codde to create the relationships thats easy to
use - i can discuss offline if needed
 
Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim
 
what is a treeview ?

I guess at most a product dimension for me may have 4 levels and at the
lowest level maybe 500 products although this figure may be 50 depending on
who uses the sheet , lets say 50 codes who have 30 parents who have 4 parents
with a master code at top of hierarchy
 
best example could be

red balls
yellow balls
green balls

all reporting to snooker which in turn reports to games
 
A treeview is like the "folder" view in windows explorer. Isn't that the
kind of thing you'd need in order for your user to visualize the connections
they're making?

Tim
 
dear tim sorry missed your reply

yes a treeview would be excellent - especially if it could add or delete new
members and/or move the members to different places

for any hierarchy i always want a level , a name , a member , a parent or
child - property against any member in the tree

so can excel do this or do we have an OCX control - as yet i have not seen
one and i am feeeling excel cannot do this elegantly
 
There is a treeview component you can use in VBA - if not in the toolbox
then right-click and choose "additional components" and look for "Microsoft
treeview..."

It will not hold all of the specific properties you list, but you should be
able to hold those in a collection of objects or similar construct and just
use the treeview to display the realtionships.

Tim
 
Back
Top