Align text in ActiveX control or some way of doing it

[GK in the UK]

Just working on getting my tree view form (thanks MajP !) populated with data.

I have the text column and a debit and credit column. How can I get them to line up ?

I see that the tree view window is an ActiveX control so I don't know if there are any formatting options.

I suppose I could go to fixed width fonts but was hoping for a prettier solution.


Edit: Of course there's a complication in that the values can come in on any of the branch levels but they must all have an aligned decimal point

 

[Uncle Gizmo]

I'm bumping your post up the list in the hope that it will get some more views, and hopefully and answer!

 

[MajP]

The value you are showing is a concatenated field. The only way I can think of how to do this is to roll your own spaces. If you can provide the query for the tree view I can come up with a function. Then you would use that in the query.

I assume you want

Legal Fees                  234.56
Accounting Fees            4567.00
Other Fees             123,6789.10

Which means you have to pad the string between the name and the fee. This is doable, but not a simple task. You would need to know the max length of the titles, max length of the values and then determine how far to pad. FYI, even if this had formatting option, it could not be done. The simplest option so that you do not have to calculate the max (which means looping all records) is to set it at an arbitrary distance. Lets say 100 characters. This could end up looking like

Legal Fees                                           234.56
Accounting Fees                                     4567.00
Other Fees                                      123,6789.10

But at least you can put in enough distance to be safe.

 

[isladogs]

If it helps, I have code that produces output like this where values are aligned :

To do this, you do need to use a monospaced font and the code is indeed more tricky than you might expect

 

[GK in the UK]

Thanks, MajP, and again for the TVW module. I need to find a way to do this. I temporarily set the font to a retro Courier which aligns each branch but I still haven't solved the different offsets.

Setting the text to a fixed width to accommodate all possibilities isn't a problem. The text is up to 40 long then I have a debit column and a credit column each 16 characters wide so minimum 72.

The query isn't trivial. I have to sum debits and credits according to type of record and get the branch totals separately plus drop in user parameters. Having said that I could return the values as a signed value and format them in the code debit or credit, but it works as it is.


Here it is. It boils down to nodeText, nodeDrValue and nodeCrValue

SELECT "RecID" & [NominalID] AS ID, "RecID" & [nominalParent] AS parentID, tblNominalAccounts.nominalName AS nodeText, "RecID" AS identifier, tblNominalAccounts.nominalType, Sum(tblTransLines.tlNetValue) AS NetSum, IIf([nominalType]="H",IIf([Balance_Grp]>0,[Balance_Grp],Null),IIf([NetSum]>0,[NetSum],Null)) AS nodeDrVal, IIf([nominalType]="H",IIf([Balance_Grp]<0,-[Balance_Grp],Null),IIf([NetSum]<0,-[NetSum],Null)) AS nodeCrVal, Sum(IIf([thAcYrPrd]<=[EndAcYrPrd] And [nominalType]="B",Nz([tblTransLines].[tlNetValue],0),Null)) AS Balance_BS, Sum(IIf(([thAcYrPrd]>=[StartAcYrPrd]) And ([thAcYrPrd]<=[EndAcYrPrd]) And ([nominalType]="P"),(Nz([tlNetValue],0)),Null)) AS Balance_PL, Sum(IIf([nominalType]="H",fGroupTotal([nominalID],[StartAcYrPrd],[EndAcYrPrd],[ThPbNo]),0)) AS Balance_Grp
FROM tblTransHeaders RIGHT JOIN (tblTransLines RIGHT JOIN tblNominalAccounts ON tblTransLines.tlNominalFK = tblNominalAccounts.NominalID) ON tblTransHeaders.TransHeaderID = tblTransLines.tlTransHeaderFK
GROUP BY "RecID" & [NominalID], "RecID" & [nominalParent], tblNominalAccounts.nominalName, "RecID", tblNominalAccounts.nominalType
HAVING (((tblNominalAccounts.nominalType)="H")) OR (((Sum(tblTransLines.tlNetValue))<>[thValue]) AND ((Max(tblTransHeaders.thPostingBatchNo))>=[thPbNo]));

 

[GK in the UK]

Hang on I'll format the SQL


'SELECT "recid" & [nominalid] AS ID,
' "recid" & [nominalparent] AS parentID,
' tblnominalaccounts.nominalName AS nodeText,
' "recid" AS identifier,
' tblnominalaccounts.nominaltype,
' Sum (tbltranslines.tlNetValue) AS NetSum,
' Iif([nominaltype] = "h", Iif([balance_grp] > 0, [balance_grp], NULL), Iif( [netsum] > 0, [netsum], NULL)) AS nodeDrVal,
' Iif([nominaltype] = "h", Iif([balance_grp] < 0, -[balance_grp], NULL), Iif( [netsum] < 0, -[netsum], NULL)) AS nodeCrVal,
' Sum( Iif([thacyrprd] <= [endacyrprd] AND [nominaltype] = "b", Nz([tbltranslines].[tlnetvalue], 0), NULL)) AS Balance_BS,
' Sum(Iif(( [thacyrprd] >= [startacyrprd] ) AND ( [thacyrprd] <= [endacyrprd] ) AND ( [nominaltype] = "p" ), ( Nz([tlnetvalue], 0) ), NULL)) AS Balance_PL,
' Sum(Iif([nominaltype] = "h", Fgrouptotal([nominalid], [startacyrprd], [endacyrprd], [thpbno]), 0)) AS Balance_Grp
'FROM tbltransheaders
' RIGHT JOIN (tbltranslines
' RIGHT JOIN tblnominalaccounts
' ON tbltranslines.tlnominalfk = tblnominalaccounts.nominalid)
' ON tbltransheaders.transheaderid = tbltranslines.tltransheaderfk
'GROUP BY "recid" & [nominalid],
' "recid" & [nominalparent],
' tblnominalaccounts.nominalname,
' "recid",
' tblnominalaccounts.nominalType
'HAVING (((tblnominalaccounts.nominalType) = "h")) OR ( ( ( Sum(tbltranslines.tlnetvalue) ) <> [thvalue] ) AND ( ( Max(tbltransheaders.thpostingbatchno) ) >= [thpbno] ) );

 

[isladogs]

It would be far better if you used code tags.
BTW the code I use also manages cases where the initial text is indented as in a treeview

 

[GK in the UK]

Definitely interested to know how it can be done Colin

 

[isladogs]

Just about to sign off for tonight but can post the code I used tomorrow.
Basically its just a matter of counting using code!

 

[MajP]

Not really sure how you got the tree to populate. The intent of the class was to produce a standard query format that would load the tree without code modification, I doubt that is possible with what you show. It appears your node text is just the name and somehow you are loading fees. So I do not know how you are loading the node text. If this was me I would have done it with a union quuery of 4 queries (based on the four levels you show). Then my node text would be formatted like I suggested (pretty sure it is the same as what Colin proposes). You count the length of the name "Building / Properties". You count the length of the fee (1.00). Add that together. You subtract from the allowable length and get the number of characters needed to pad to equal the allowable length. Make a string of that pad length. Then return "Name" & string(padLength,".") & Fee.
If you are suggesting you want all levels aligned as well, then you are going to have to subtract a pad amount for each level. That you will have to play with. I know of no way to determine how far each level is indented. The code does provide a method for determining the level.

Somewhere you are going to have to do that, but since I am not certain how you populate the tree, I cannot tell you where in the code to do it.
If I was doing it, I would not do any modification to the class. My query to populate the tree would have a properly padded NodeText, which would be a simple function call in the query.

My query would look like
NodeText: GetPaddedText([NominalName],[Fee],250)

My function would have a signature
Public GetPaddedText(NominalName as string, Fee as currency,MaxLength as integer)
get length of nominal name
get length of fee
padlength = maxLength - fee length - name length
GetPaddedText = NominalName & string(padLength,".") & Fee
end function

If you align levels then pass in the level and
padLength = maxLength - fee length - name length - level(someNumberOfCharacters)

 

[GK in the UK]

Two parts to the discussion now:

The formatting, I didn't know what to expect, I wondered if maybe there was some way of tabbing over some string or text in the control to line it up. But if it's a case of padding out a fixed-width font, I can do that, I'm already doing that for the branches so I just need to adjust it for the different levels. I need the text to line up on every branch at any level (within reason say 4, maybe 5 levels). For the indentation on each level, I'll have to play with the TreeView.Indentation value based on the current level so that the indentation is a multiple of a character width.

Population of the tree view control: MajP, I followed your notes. Every branch/leaf is either a record from a table, or a group of records from the same table, both with the total value of all related TransLines. My records already had the ParentID field for the time when I got around to addressing this feature. So I started with the 4 control fields you defined, ID, parentID, identifier, and nodeText, then added the query columns for the numeric totals I need. So population should be as you intended. But, I added a couple of lines to your sub addBranch to allow for the two numeric columns on each node. You're suggesting I leave the class alone and do the string formatting in the query. I'll probably do that now I know I have to format the text.

Populating the actual vales, now that was/is challenging and may be a new post. The totals that I want to show don't exist anywhere, they're calculated on the fly. So what I have is three types of record coming from the same table, and each of the three types has a different summing method. Two are just fetching the related transactions with a total, albeit with different criteria. The third, the group headings is done in function fGroupTotal in the query. This is tricky, because this function has to discover *all* of the children of the record, make a list, and then sum *all* transactions for *any* of the records in the list. It's very slow but I'm hatching a new plan to speed it up.

 

[MajP]

The formatting, I didn't know what to expect, I wondered if maybe there was some way of tabbing over some string or text in the control to line it up.

It is just a text value, so unfortunately there are no properties to adjust.

But if it's a case of padding out a fixed-width font, I can do that, I'm already doing that for the branches so I just need to adjust it for the different levels. I need the text to line up on every branch at any level (within reason say 4, maybe 5 levels). For the indentation on each level, I'll have to play with the TreeView.Indentation value based on the current level so that the indentation is a multiple of a character width.

So three ways to do this since the level is not known until you add the node.
1. In the recursive call when you add a node you pass in the parent record. From the parent record you can get the parent node. From the parent you can get it level. Add 1 to the parent level and add/remove spaces for the level
2. My goal is to never mess with the class module. If you read the the thread with dgreen I do the very first example of the recursive call outside of the tree for demonstration of a recursive call. So you could update the levels prior to running the treeview code. Then you could format your levels based and node text ahead of time, right in the query.
3. You could populate the tree nodes then after loop the node collection and reformat the text.

Population of the tree view control: MajP, I followed your notes. Every branch/leaf is either a record from a table, or a group of records from the same table, both with the total value of all related TransLines. My records already had the ParentID field for the time when I got around to addressing this feature. So I started with the 4 control fields you defined, ID, parentID, identifier, and nodeText, then added the query columns for the numeric totals I need. So population should be as you intended. But, I added a couple of lines to your sub addBranch to allow for the two numeric columns on each node. You're suggesting I leave the class alone and do the string formatting in the query. I'll probably do that now I know I have to format the text.

If it was me I would do everything I could to avoid altering the class module. Your take some of the flexibility away, and IMO an easier approach. If you understand the jist of the query as long as you have the formatted text, the record id, the indentifier (a reference to what table it came from), and the parent ID you should be able to load any tree. See next comment but I would consider a temp table, pre-processing, or post-processing of the tree before altering the class.

Populating the actual vales, now that was/is challenging and may be a new post. The totals that I want to show don't exist anywhere, they're calculated on the fly. So what I have is three types of record coming from the same table, and each of the three types has a different summing method. Two are just fetching the related transactions with a total, albeit with different criteria. The third, the group headings is done in function fGroupTotal in the query. This is tricky, because this function has to discover *all* of the children of the record, make a list, and then sum *all* transactions for *any* of the records in the list. It's very slow but I'm hatching a new plan to speed it up

1. This may be faster to make a temp table to replace the query. This way you might be able to utilize several aggregate queries to quickly populate the temp table. Especially since I assume you have to do recursion to get the values, it may be faster and easier.
I assume you are doing some kind of recursion here to sum up the levels. If you did recursion first and populate the levels then you could work your way up. Assume you had 5 levels. Loop all records in level 4 and get the sum of its children and populate. Now do the same for level 3, ....
I would think with this approach you could get all of your summations and format your text ahead of time. I think this could be done quickly and avoid any code modification. This would be easy to debug as well since you see your results in the temptable. This also may simplify some of the querying.
In this case where your functions are looping records, a temp table can be much faster.

I am assuming you need to do this

Level 1 node: 150 (sum level 2 nodes)
--- Level 2 Node: 100
--- Level 2 Node: 50 (sum of level 3 nodes)
--------Level 3 Node: 20
--------Level 3 Node: 30

 

[GK in the UK]

Your hierarchy schema is exactly what I've got. Currently I'm repeat counting the lowest levels so that's why it's really slow. I was already hatching a plan to use a temp table to save the record totals. Currently I put the list of decendants for every Group record into a temp table then sum all transactions that are IN(Select ID from tblDescendants) but I need to do something on the lines of what you're suggesting.

I pasted the code for TreeviewForm back in from the original file (post #144).

I forgot to mention, as it didn't seem to be a problem, I get an error, User-Defined type not defined with GetLevelIDs highlighted.

The work 'Dictionary' exists only in GetLevelIDs. I had to comment out the entire function GetLevelIDs to compile my project.

But what is odd,is that if I load up your TreeviewDemo v11, it all works and the function doesn't need commenting out.

So I don't know what's going on here or if I will need a call to GetLevelIDs at some point. I wonder if I've not brought over a module.

 

[MajP]

The GetLevelIDs is the feature to do the paragraph levels which you are not using. This was a requirement from @dgreen which I thought was very useful for building things like WBS. I list all needed references in the class notes. The dictionary object in is "scripting runtime" I believe.
1
--1.1
--1.2

Currently I'm repeat counting the lowest levels so that's why it's really slow

Without seeing the data, that is why I am guessing a temp table may work. You only have to span the "tree" once.
Not sure if you are familiar with the concept of Dynamic Programming, but in recursive problems this technique can speed things up. You probably do it without knowing. These are solutions where instead of completing the entire recursion you are saving partial solutions as you go and then building off of those

Dynamic Programming is mainly an optimization over plain recursion. Wherever we see a recursive solution that has repeated calls for same inputs, we can optimize it using Dynamic Programming. The idea is to simply store the results of subproblems, so that we do not have to re-compute them when needed later.

So instead of spanning the whole branch it may be faster to just span all of the lowest levels and save the results into the next level. Then span all of the next lowest level and save the results.

If you can share your data I could take a look.

 

[GK in the UK]

Thank you MajP. I can attach the records that populate the tree. I've been having a close look at your code this evening to see where I can get the Level from to set the text width. As I understand it, the queries can't determine that unless they're based on the recursive routine to populate the tree. Currently I'm not doing recursion to get the Group "H" values, my function does it. I populate a temp table with just a list of descendants, then sum the TransLines that are related to any of these descendants.

In your Option 3. above I think you're saying, populate the tree then loop the form recordset and re-format ? So won't the user see that the text changes position ?

You'll see there are 3 types of record, and I have 3 queries to deal with them which consolidate to a Union query which populates the tree view.

User puts in parameters (currently hard coded into the queries for testing)

Currently done in the queries:
Record type "P", I need sum of all TransLines between start and end
Record type "B", I need sum of all TransLines up to end
Record type "H", no TransLines are ever related but the other types may have this type as a Parent. Calls the sum function.

There's the code which I use to sum the descendants which is clunky and doesn't make the grade because of the repeated summing issue.

I also need, although if necessary I can do a separate query just to get these totals, even a Dsum would do it, the sum of all the "P" type records and the sum of all the "B" type records with the same start and end criteria.

Any input appreciated.

 

[isladogs]

Definitely interested to know how it can be done Colin

Apologies for the slow response. I've been busy on unrelated activities and not spent any time on my main computer
The code below is used as part of a lengthy procedure lasting about 30-45 minutes and used to synchronise data in one of my schools databases each night with data from an external database. After each step in the procedure, a sub called WriteTextEntry is used to list the details in a new line together with the overall time of completion and whether that part of the procedure was successful or not. In some cases there are 'sub - sub procedures' which are indented.

As the sync update is designed to run unattended overnight, normally there will be nobody viewing the procedure as it runs. Therefore a logfile is simultaneously created and automatically emailed to the program admin on completion for checking the next morning.

The main code calculates the length of the procedure summary info, the length of the times string and adds the required number of dots or spaces to align each item perfectly using a monospaced font - in this case Courier New.

The name of the textbox is txtErrors

Sub WriteTextEntry(strEntry As String)
On Error GoTo err_writetextentry
Dim L, S, T, icount As Integer

L = Len(strEntry)
S = Len(strSuccess)
T = Len(Format((ShowTickCount / 1000), "0.00") & "s")

If Left(strEntry, 1) = "=" Then 'for header / footer . . .omit timing
    strEntry = strEntry
Else 'show OK/Fail & timing
    If Len(strEntry) < 105 Then  ' - value changed from 60 to 105 to increase width
    'strEntry = strEntry & String(80 - L, ".") & strSuccess & String(15 - S - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "   "
        If 98 - L > 0 Then
       '     strEntry = strEntry & String(83 - L, ".") & strSuccess & String(15 - S - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "   "
            strEntry = strEntry & String(83 - L, ".") & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "    " & strSuccess
        Else
       '     strEntry = strEntry & strSuccess & String(15 - S - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "   "
            strEntry = strEntry & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "    " & strSuccess
        End If
    Else ' - value changed from 58 to 100 to increase width
      '  strEntry = Left(strEntry, 100) & String(2, ".") & strSuccess & String(15 - S - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "   "
        strEntry = Left(strEntry, 100) & String(2, ".") & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & "    " & strSuccess
    End If
End If

If IsOpen("frmMain") Then
    strErrMsg = strErrMsg & ";" & strEntry
    Forms!frmMain.TabCtl.Value = 0
    Forms!frmMain.txtErrors.RowSource = strErrMsg
    Forms!frmMain.txtErrors.Requery
    icount = Forms!frmMain.txtErrors.ListCount - 1
    Forms!frmMain.txtErrors = Forms!frmMain.txtErrors.ItemData(icount)
    Forms!frmMain.Repaint
End If

exit_writetextentry:
    Exit Sub
   
err_writetextentry:
    If Not AutoFlag Then
        MsgBox err.Description
    End If
    'Resume Next '
    Resume exit_writetextentry

End Sub

The ShowTickCount function is in a standard module:

Option Compare Database   'Use database order for string comparisons
Option Explicit

'###############################################
'Add PtrSafe / LongPtr - required for 64-bit Office (VBA7)
#If VBA7 Then '64 bit Office
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongPtr
#Else '32-bit Office
    Declare Function GetTickCount Lib "kernel32" () As Long
#End If
'#############################################

Global TickCount As Long
Global lngTicks As Long

Public Sub StartTickCount()
   TickCount = GetTickCount()
End Sub

Public Function ShowTickCount() As Long
   ShowTickCount = GetTickCount() - TickCount
End Function

I hope you can make sense of the basic idea from the above.
If you have questions, please feel free to ask

 

[MajP]

1.

I've been having a close look at your code this evening to see where I can get the Level from to set the text width.

The class has a function called getNodLevel() and SelectedNodeLevel() to return the level of the selected node or any node.


2.

As I understand it, the queries can't determine that unless they're based on the recursive routine to populate the tree.

Depends on how you set up your query. Is it a pure self referncing query, a union query of 1 to manys, or some combination. If you look in my example I do a union query of Customers Orders and Orders_Details. Using the tricks I make this look like a self referencing table, but it is just 2 one to manys unioned together (customers to orders, order to order details). That is a big trick that I discuss in the long thread which allows you to make a tree out of one to manys. In this case you would know the levels ahead of time depending if the node is a customer, order, or order detail.

However if I had a real self referencing table (Assembly sub assembly, sub-sub assembly.., or family tree child, parent, grand parent, great...) there is no way to know the level without recursion.

Part 1
---- Part A
---- Part B
Part 2
---- Part C
--------- Part aa
--------- Part bb

So not sure of the question

3.

In your Option 3. above I think you're saying, populate the tree then loop the form recordset and re-format ? So won't the user see that the text changes position ?

There are some advantages to populating the tree first and then formatting. The tree provides a nodes collection, and you can loop then easily. In one of the demos. I populate the tree then format the levels with different fonts, and the nodes with different colors based on content. The code is simpler this way than trying to do this and load the tree at the same time. It becomes a simple loop, however at this time you have a means to call the getNodeLevel(). It is fast and you do not see a flicker. The user does not know that it is loaded then formatted, it just appears formatted.

I am thinking for what you want to do there are several approaches and it would come down to what code is the cleanest and what method is fast. I think you could do this before loading the tree from a temp table or load the tree and do the formatting and roll ups afterwards.

I really could not figure out what you posted. I think you stripped out too much, none of the queries worked. Can you post the entire db to include the relevant tables and tree?

 

[GK in the UK]

Ah, sorry about the file. I wasn't sure what you meant by data, I included the table that populates the tree view.

There are some advantages to populating the tree first and then formatting.

That's what I'm doing. Populate the tree, count the levels to determine the minimum amount of padding, then pad the node text. So all levels are aligned perfectly on the decimal point in two numeric columns. I determined that with Courier 9, TreeView.Indentation must be set to 310. You are correct there's no flicker or anything to see as the text is padded.

This may be faster to make a temp table to replace the query

Yes. Load time has been slashed to 5 seconds, that's summing 45k transactions into about 300 categories and maybe 100 group totals so I'm happy with that.

I'm having trouble getting the form criteria into the query but I'm making a separate post.

Thanks for the help.

 

[MajP]

I determined that with Courier 9, TreeView.Indentation must be set to 310.

Can you provide some details. I will put a note in the class on how to align levels in case others are interested. Are you saying each level is indented at about 310 characters?

[QUOTEI'm having trouble getting the form criteria into the query but I'm making a separate post.
[/QUOTE]
You may want to take a look at this. I am not a fan of any form criteria in a query.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

I need to add to that thread on how to build the query from the filter. I will try to update that today.

 

[GK in the UK]

Yes, I'm saying when I set TreeView.Indentation = 310, font.name = "Courier New", font.size = 9, all levels are exactly 3 characters indented. It turned out I already had 7 levels so I made code to count the levels and adjust the padding to the minimum that was needed to pad out the top level so that it aligned with level 7. Actually level 1 may have been adrift but it doesn't matter for me, I'm not showing any numbers in the root.

Here's my code, which I'm slightly nervous about posting, because I have a sneaking suspicion it could be done better, or that I might actually have some of the values I'm looking up, but a point came when it worked and I had to leave it be.

Public Function getLevel(ByVal theNode As Node) As Integer
  getLevel = 1
  Do Until theNode.Root = theNode.FirstSibling
    getLevel = getLevel + 1
    Set theNode = theNode.Parent
  Loop
End Function

Function fOffset(intLevels As Integer, intCurLevel As Integer) As String
  ' set TreeView.Indentation = 310, font.name = "Courier New", font.size = 9
  ' each level will be 3 character offset
  ' level 1 will *not* be formatted but we have no value in this level
  Const spaces = "..."  ' 3 characters wide
  Const offset = 3      ' 3 characters per level
  Dim pad As String
  While Len(pad) < (intLevels * offset)
    pad = pad & spaces
  Wend
  fOffset = Left(pad, ((intLevels * offset) - (intCurLevel * offset)))
End Function

Private Sub CountLevels(theNode As Node, intLevels As Integer)
  ' count the number of levels in the populated tree view
  Dim n As Node
  Dim i As Integer
  Dim lvl As Integer
 
  'using the 'Child' property of the node, get the first child
  Set n = theNode.Child
  For i = 1 To theNode.Children
     lvl = getLevel(n)
     CountLevels n, intLevels
     'using the 'Next' property of the current child, get the next child
     Set n = n.Next
     If lvl > intLevels Then intLevels = lvl
  Next i

End Sub

Private Sub AlignNodeText(theNode As Node, intLevels As Integer)
  ' we pass in the number of levels that we already counted,
  ' then we can pad out the levels so they all align.  the
  ' rightmost level will have no padding, that will determine
  ' how far out the text will be.
   Dim n As Node
   Dim i As Integer
   ' using the 'Child' property of the node, get the first child
   Set n = theNode.Child
   'traverse the children using the 'Children' property of the node
   For i = 1 To theNode.Children
      ' adjust the text width of the child node
      n.Text = Left(n.Text, 40) & fOffset(intLevels, getLevel(n)) & Mid(n.Text, 41)
      ' recursive call to all nodes
      AlignNodeText n, intLevels
      ' using the 'Next' property of the current child, get the next child
      Set n = n.Next
   Next i
   ' indicate no children
   If i = 0 Then Debug.Print "Node has zero children"

End Sub