hierarchys - can you build an elegant hierarchy using vba

[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

 

[Guest]

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

 

[Guest]

experts - I assume excel can not do this ...............

can anyone help ??

 

[Tim Williams]

What is it that you want your drill up/down function to do?

Tim

 

[Tushar Mehta]

You can do quite a bit with XL and VBA as long as you have the time and/or
money to invest in it.

For a drilldown example see
PivotChart Drill-down
http://www.tushar-mehta.com/excel/software/pivotchart_drilldown/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

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

 

[Tim Williams]

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.

 

[Guest]

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

 

[Guest]

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

 

[Tim Williams]

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

 

[Guest]

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

 

[Guest]

best example could be

red balls
yellow balls
green balls

all reporting to snooker which in turn reports to games

 

[Tim Williams]

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

 

[Guest]

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

 

[Tim Williams]

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

 

[Guest]

thank you i will work with this - it might do the job