Strongs
asked on
Re-number Treeview nodes after deleting a child node
I have a treeview on a userform in MS Word. The treeview is populated from an excel spreadsheet using a ADO connection. There are two columns in the sheet: Task No and Description.
The Task No column contains a unique entry in the form of numbers formatted as text:
1
1.1
1.1.1
1.1.2
2
2.1
and so on. The Description column just contains text corresponding to the Task No cell.
I can populate the treeview without any issues and add, update the nodes within it. However, if I want to delete a node, this is far more complex. For example, If I want to delete 1.1 and all children beneath it e.g. 1.1.1, 1.1.1.1, 1.1.2 etc; then the next child node and all children beneath that should be renumbered, starting from the node that has been deleted. e.g. 2.1 becomes 1.1, 3.1 becomes 2.1 etc;
The key for the nodes consists of the Task No with an alpha character appended to the end e.g. "1.1a" and the text for the node consists of the Task No, a hyphen and the description e.g. "1.1-Description text".
here is the code that I have so far:
I would also like to implement drag drop on the treeview so that users can drag nodes to another position and I am having trouble getting this to work. It will be a similar situation where nodes are renumbered to account for the movement of nodes further up or down the tree.
Any help gratefully received.
The Task No column contains a unique entry in the form of numbers formatted as text:
1
1.1
1.1.1
1.1.2
2
2.1
and so on. The Description column just contains text corresponding to the Task No cell.
I can populate the treeview without any issues and add, update the nodes within it. However, if I want to delete a node, this is far more complex. For example, If I want to delete 1.1 and all children beneath it e.g. 1.1.1, 1.1.1.1, 1.1.2 etc; then the next child node and all children beneath that should be renumbered, starting from the node that has been deleted. e.g. 2.1 becomes 1.1, 3.1 becomes 2.1 etc;
The key for the nodes consists of the Task No with an alpha character appended to the end e.g. "1.1a" and the text for the node consists of the Task No, a hyphen and the description e.g. "1.1-Description text".
here is the code that I have so far:
If Me.tvScalar.SelectedItem Is Nothing Then
MsgBox "Please select a node", vbInformation, "No node selected"
Exit Sub
End If
nodeKey = tvScalar.SelectedItem.Key
nodeBool = countSeparators(nodeKey, ".")
If nodeBool = 0 Then
MsgBox "Cannot delete Training Objectives"
Exit Sub
ElseIf nodeBool = 1 Then
MsgBox "Cannot delete an Enabling Objective"
Exit Sub
End If
txtMsg = MsgBox("Deleting a parent task will delete all tasks below it. This action cannot be undone. Are you sure you want to delete this task?", vbExclamation + vbOKCancel, "Delete task")
If txtMsg = vbOK Then
'get the scalar nodes
Set objRecordset = New ADODB.Recordset
'form the connection string. Use IMEX=0 to make readwrite
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties='Excel 8.0;IMEX=0'"
sqlString = "SELECT [Task No], [Description] FROM [Task Report$] ORDER BY [Task No];"
'create recordset. Use adOpenDynamic to make editable
objRecordset.Open sqlString, connString, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic
'we need to do some preparatory work first, getting node references
'*****************************************************************
'get a reference to the selected node
Set nd = Me.tvScalar.SelectedItem
selNodeIndex = nd.Index
'get the key of the parent node of the node that is selected
Set tvParentNode = nd.Parent
tvParentKey = tvParentNode.Key
'trim the trailing 'a' from the parent node key
tvTrimParentKey = Left(tvParentKey, Len(tvParentKey) - 1)
'get a reference to the next node in the chain for the one selected
If Not nd Is Nothing Then
Set tvNextNode = nd.Next
' Set nd = Me.tvScalar.SelectedItem
End If
'preparatory work done
'*******************************************************************
'now lets start going through the selected nodes chidren and setting any reference to them
'in the Excel Task Report to null. Cannot delete a row in excel so the only option is to set
'values to null.
If Not nd Is Nothing Then
For i = selNodeIndex To nd.LastSibling.Index - 1
'set reference to the selected item
Set tvNode = Me.tvScalar.Nodes.Item(i)
'get the key of this node
tvKey = tvNode.Key
'trim off the trailing 'a'
tvTrimKey = Left(tvKey, Len(tvKey) - 1)
'now lets set the value of the Task No and Description for current node to null
With objRecordset
If Not .EOF Then
.Find "[Task No]=" & "'" & tvTrimKey & "'"
If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimKey) Then
If Not .EOF Then
Debug.Print .Fields("Task No").Value
' .Fields("Task No").Value = vbNullString
' .Fields("Description").Value = vbNullString
' .Update
End If
End If
'remove the item
.MoveFirst
End If
End With
Next i
End If
Me.tvScalar.Nodes.Remove (nd.Index)
Set nd = Nothing
//******This is where help is needed************************************************
'now we need to re-number the remaining nodes in the selected chain
For i = tvParentNode.Index To tvParentNode.LastSibling.Index
Set tvNextNode = tvScalar.Nodes.Item(i)
'trim off the trailing 'a' from the current node key
tvNextKey = tvNextNode.Key
tvTrimNextKey = Left(tvNextKey, Len(tvNextKey) - 1)
'count the number of separators in the current node key
tvKey = tvNextNode.Key
count = countSeparators(tvKey, ".")
Select Case count
Case 0
'do nothing as this is a TO
Case 1
'do nothing as this is an EO
Case 2
'KLP
tvTrimKey = tvTrimParentKey & "." & tvParentNode.Children
'find the current node and set the Task No to the new key (tvTrimKey)
With objRecordset
If Not .EOF Then
.Find "[Task No]=" & "'" & tvTrimNextKey & "'"
If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimNextKey) Then
If Not .EOF Then
Debug.Print tvTrimNextKey
' .Fields("Task No").Value = tvTrimKey
' .Update
End If
End If
.MoveFirst
End If
End With
Case 3
tvTrimKey = tvNextNode.LastSibling.Key & "." & tvParentNode.Children
'find the current node and set the Task No to the new key (tvTrimKey)
With objRecordset
If Not .EOF Then
.Find "[Task No]=" & "'" & tvTrimNextKey & "'"
If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimNextKey) Then
If Not .EOF Then
Debug.Print tvTrimNextKey
' .Fields("Task No").Value = tvTrimKey
' .Update
End If
End If
.MoveFirst
End If
End With
Case 4
tvTrimKey = tvNextNode.LastSibling.Key & "." & tvParentNode.Children
'find the current node and set the Task No to the new key (tvTrimKey)
With objRecordset
If Not .EOF Then
.Find "[Task No]=" & "'" & tvTrimNextKey & "'"
If Not IsNull(.Fields("Task No").Value) And (.Fields("Task No").Value = tvTrimNextKey) Then
If Not .EOF Then
Debug.Print tvTrimNextKey
' .Fields("Task No").Value = tvTrimKey
' .Update
End If
End If
.MoveFirst
End If
End With
End Select
Next i
'clean up recordset
objRecordset.Close
Set objRecordset = Nothing
Else
Exit Sub
End If
Function countSeparators(myString As String, mySeparator As String) As Integer
countSeparators = UBound(Split(myString, mySeparator))
End Function
I would also like to implement drag drop on the treeview so that users can drag nodes to another position and I am having trouble getting this to work. It will be a similar situation where nodes are renumbered to account for the movement of nodes further up or down the tree.
Any help gratefully received.
2 is not a child of 1 and should not be renumbered.
ASKER
so, your description should have stated that 1.2 becomes 1.1 and 1.2.1 becomes 1.1.1?
ASKER
That's right. The dark red nodes will never be deleted(1,2,3 etc;).
If 1.1 is deleted and by default all of its children, then 1.2 and all of its children then become 1.1, 1.1.1 etc. 1.3 becomes 1.2 and so on.
If 1.1 is deleted and by default all of its children, then 1.2 and all of its children then become 1.1, 1.1.1 etc. 1.3 becomes 1.2 and so on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Spot on!