Solved: Use textbox to display data related to the child node

[Aussiebear]

As part of an ongoing project, I would like upon mouse over of a value in a tree view control to display related data in a text box. How is this done?

In a previous thread (http://www.vbaexpress.com/forum/showthread.php?t=19736), I had 4 teams of 4 players. Bob & Joseph kindly showed how the treeview control works, but I now need to take this one step further.

As each of the players (child node value) is moused over, the text box on the form needs to show the data that is related to that person.

[mdmackillop]

Hi Ted,
I can't see a MouseOver event. A basic click event (needing refinement) is:

Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
Dim Rng As Range, r As Range, txt As String
Set Rng = Sheets("Player Data").Columns(1).Find(Node).Resize(, 4)
For Each r In Rng
txt = txt & r & " "
Next
TextBox1 = Trim(txt)
End Sub



BTW. You should qualify your code to populate the treeview when started from any sheet.

[Aussiebear]

Qualify?

[Bob Phillips]

I had to rwerite the load code as you change the data structure



Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
Dim LastRow As Long
Dim Pos As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Pos = .Evaluate("MATCH(1,(""" & Left$(Node.Key, 1) & """=B2:B" & LastRow & ")*" & _
"(""" & Right$(Node.Text, Len(Node.Key) - 1) & """=A2:A" & LastRow & "),0)")
TextBox1.Text = Node.Text & " - Team " & Left$(Node.Key, 1) & ", Position: " & .Cells(Pos, "C").Value & ", Games: " & .Cells(Pos, "D").Value
End With

End Sub

Private Sub Userform_Activate()
Dim oNode As Node
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

Set sh = ActiveSheet

With TreeView1.Nodes

TreeView1.LineStyle = tvwRootLines

LastRow = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRow

If sh.Cells(i, "B").Value <> sh.Cells(i - 1, "B").Value Then

.Add Key:=sh.Cells(i, "B").Value, Text:=sh.Cells(i, "B").Value
End If

Set oNode = .Add(Relative:=sh.Cells(i, "B").Value, _
Relationship:=tvwChild, _
Key:=sh.Cells(i, "B").Value & sh.Cells(i, "A").Value, _
Text:=sh.Cells(i, "A").Value)
Next i
End With
End Sub

[Aussiebear]

Sorry Bob, but that code gives me an incorrect Treeview listing. I've gone back to the initial code which works fine.

Malcolm's code works, but brings up the data in a form which I understand but others wouldn't. Since I'm not worried at this stage about the correct layout of the data in the textbox, I am marking this thread solved.

Thank you both for your help.

[mdmackillop]

Qualify?
Your code will only run from one sheet. You need to add
With sheets("Teams")
etc.
if you want to open your userform from elsewhere.

[Aussiebear]

The intention is to load the Userform on opening the workbook. Only an admin will have access to the worksheets.

I take your point about referencing the correct sheet.