SearchBox for Treeview

zplugger · Jan 10th 2015

Is it possible to have a search box search a treeview on userform,so if you type in a textbox it searches the treeview list and fill another textbox with the data.
Thanks Z

cytop · Jan 10th 2015

Re: SearchBox for Treeview

A TreeView does not have an in-built Search function, but you can iterate fairly quickly through the nodes comparing text.

For an example, add a TreeView, TextBox and ListView to a userform. Leave all control names as default and paste the following code into the form

Code

Option Explicit
Option Compare Text




Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


   Dim n As Node
   
   ListBox1.Clear
   
   '// If nothing to search for, then prevent all nodes added
   '// to th elistbox.
   If TextBox1.Text = vbNullString Then Exit Sub
   
   TextBox1.Locked = True
   
   For Each n In TreeView1.Nodes
      
      If n.Text Like "*" & TextBox1.Text & "*" Then
         ListBox1.AddItem n.Text
      End If
   Next
   
   TextBox1.Locked = False
   
End Sub




Private Sub UserForm_Initialize()


   TreeView1.Nodes.Add , , "root", "Root"
   TreeView1.Nodes.Add "root", tvwChild, "abc", "ABC"
   TreeView1.Nodes.Add "root", tvwChild, "abD", "ABD"
   TreeView1.Nodes.Add "root", tvwChild, "abe", "ABE"
   TreeView1.Nodes.Add "root", tvwChild, "abr", "ABR"
   
   TreeView1.Nodes.Add "abe", tvwChild, , "ABC123"
   TreeView1.Nodes.Add "abe", tvwChild, , "ABC223"
   TreeView1.Nodes("abe").Expanded = True
   
   Dim iTemp As Integer
   
   '// Just to test with a lot of nodes.
   For iTemp = 1 To 10000
      TreeView1.Nodes.Add "root", tvwChild, , CStr(iTemp)
   Next
   
   TreeView1.Nodes("root").Expanded = True
   
End Sub

Display More

Couldn't understand what results you wanted in a textbox so the Caption of any matching node is copied to the listbox. Just type in the textbox to search.

zplugger · Jan 11th 2015

Re: SearchBox for Treeview

Thanks cytop for the example
Could not make it work with my project,If I post it will you take a look.

http://www.excelforum.com/exce…on-userform-treeview.html

Thanks for any help I can get

cytop · Jan 11th 2015

Re: SearchBox for Treeview

You should read the board rules... and then then read http://www.excelguru.ca/content.php?184

And, as is mentioned in that other thread, you should:

Quote

Give a description of the whole requirement and not a bit at a time as this will yield much better results

zplugger · Jan 11th 2015

Re: SearchBox for Treeview

Sorry I agree that I will absolutely abide by the "cross post" rule in future. Sorry for this miss from my side and request your help please. Guess I was not thinking,what I'm trying to do is add
a search box to my project if possible.The example workbook shows what I'm looking for. Thanks for any help I can get.

cytop · Jan 11th 2015

Re: SearchBox for Treeview

It wasn't meant to work with your project - it was a stand alone example to illustrate. The code only was provided as it was written on an Android Pad and it's a little difficult to test Excel VBA code on one of those.

Your workbook from Excel Forum is attached with the code added. I do have issues with mismatched Common controls on this machine after a recent 'downgrade' so you may have problems loading the userform. If that should happen, all the changed code is below:

(Userform Declaration section - at the very top before the first procedure).

Code

'// This is required to allow case insensitive text comparisons
'// If not included, then text compares are case sensitive smd probably
'// need to be converted to all upper/lower case to compare correctly
Option Compare Text


Dim LastNode As Node
Dim lngStdBackColor As Long
Dim lngStdForeColor As Long

(TextBox8 KeyUp Event handler)

Code

Private Sub TextBox8_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


   Dim n                As Node


   '// Reset any previous Search
   '// Comment this to see default behaviour
   '// (The highlighted item may not even be visible)
   If Not LastNode Is Nothing Then
      With LastNode
         .BackColor = lngStdBackColor
         .ForeColor = lngStdForeColor
      End With
      
      Set LastNode = Nothing


   End If
   '// End of comment


   '// If nothing to search for, then nothing to do
   If TextBox8.Text = vbNullString Then Exit Sub


   '// Just make sure the Std colors have a value
   With TV.Nodes(1)
      lngStdBackColor = .BackColor
      lngStdForeColor = .ForeColor
   End With
   
   '// Just to prevent rapid keystrokes causing any issues with
   '// possible re-en trant code.
   TextBox8.Locked = True


   '// Loop all nodes...
   For Each n In TV.Nodes


      '// Option 1 - Node text Start with ...
      '// Option 2 Node text contains...
      '// If n.Text Like Textbox8.Text & "*" Then
      If n.Text Like "*" & TextBox8.Text & "*" Then
         
         '// Set a reference to the node
         Set LastNode = n
         
         '// Make sure it is visible
         With LastNode
            .EnsureVisible
            
            '// Comment this to see default behaviour
            .BackColor = vbRed
            .ForeColor = vbWhite
            
            '// and uncomment this - the highlighting will be lost.
            '// Play around with the HideSelection Proporty as well
            '// HideSelection hides the selection (!) when the
            '// control loses focus - it's very confusing...
            '// Set TV.SelectedItem = LastNode
            
            '// End of comment
            
         End With


         Exit For
      End If
   Next


   TextBox8.Locked = False


End Sub

Display More

(TV Enter event handler)

Code

Private Sub TV_Enter()


   '// Treeview has focus - cancel any highlighting of found item
   If Not LastNode Is Nothing Then
      With LastNode
         .BackColor = lngStdBackColor
         .ForeColor = lngStdForeColor
      End With
      
      Set LastNode = Nothing
   End If
   
End Sub

Display More

The way a TreeView works is slightly confusing - especially if HideSelection is set True. This hides the selected item if the TreeView does not have focus which is why the code changes the color of any item found. You need to play around with it and understand the various settings...

Just as a matter of interest: As well as adding links to any cross posts, it is also considered not the done thing to expect anyone to go to another site to download a file. If you post on a site, you include all relevant info, and attachments, in that post.

zplugger · Jan 11th 2015

Re: SearchBox for Treeview

Thank You for your time, nice job on the code. I will play around and try to get it to populate the textbox1 as it does a search. Not sure if possible,but if not
it works pretty darn good now. What is the proper way to close cross post?.
Thank You

cytop · Jan 11th 2015

Re: SearchBox for Treeview

To 'Click' the TV, replace the Textboxc8_KeyUp event hadler with:

Code

Private Sub TextBox8_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


   Dim n                As Node


   '// Reset any previous Search
   '// Comment this to see default behaviour
   '// (The highlighted item may not even be visible)
   If Not LastNode Is Nothing Then
      With LastNode
         .BackColor = lngStdBackColor
         .ForeColor = lngStdForeColor
      End With
      
      Set LastNode = Nothing


   End If
   '// End of comment


   '// If nothing to search for, then nothing to do
   If TextBox8.Text = vbNullString Then Exit Sub


   '// Just make sure the Std colors have a value
   With TV.Nodes(1)
      lngStdBackColor = .BackColor
      lngStdForeColor = .ForeColor
   End With
   
   '// Just to prevent rapid keystrokes causing any issues with
   '// possible re-en trant code.
   TextBox8.Locked = True


   '// Loop all nodes...
   For Each n In TV.Nodes


      '// Option 1 - Node text Start with ...
      '// Option 2 Node text contains...
      '// If n.Text Like Textbox8.Text & "*" Then
      If n.Text Like "*" & TextBox8.Text & "*" Then
         
         '// Set a reference to the node
         Set LastNode = n
         
         '// Make sure it is visible
         With LastNode
            .EnsureVisible
            
            '// Comment this to see default behaviour
            .BackColor = vbRed
            .ForeColor = vbWhite
            
            '// To populate the textbox, simply call the TV_Click event
            Set TV.SelectedItem = LastNode
            
            TV_Click
            
            '// TV_Click takes focis away from the Textbox - put it back
            TextBox8.SetFocus
            
         End With


         Exit For
      End If
   Next


   TextBox8.Locked = False


End Sub

Display More

You should be aware you are using the wrong event - any code to run when a node is clicked should be in the NodeClick event handler, not the TreeView Click event... It makes it easier to manipulate the nodes in code...

zplugger · Jan 11th 2015

Re: SearchBox for Treeview

Trying to add delete button for textbox8,so you can clear searchbox. I get that part but can't make the userform start back to first item in list?
Another Question what if there is two or three names the same,can it search all?

cytop · Jan 11th 2015

Re: SearchBox for Treeview

My original sample listed all found items in a list box - It does work if you add it to its own userform as I described - admittedly you then need to work it back into your own userform if you decide to use something like that. That is about the simplest way to report multiple matches. With the code as written at the moment, I'd suggest leaving it as it is, as trying to add the extra functionality is going to hit a lot of the existing code.

I did try with the delete button, but only ended up clearing the 2 sample comments by clearing TextBox1.

Sorry, but I can't give any more time to this tonight. Perhaps fresh eyes are needed...

zplugger · Jan 11th 2015

Re: SearchBox for Treeview

Thanks cytop
Getting a lot closer because of all your help. I will keep pegging away at code and try and make progress. Think this
could be handy tool for people using Excel. Good way keep track of different codes.All ideas are welcome to make this better.

zplugger · Jan 12th 2015

Re: SearchBox for Treeview

Wanted SuperHero
Need some fresh eyes on this,maybe its not possible. The search code stops at the first item of search,hoping it possible to show all items with same name.

SearchBox for Treeview

Participate now!

Share