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
SearchBox for Treeview
-
-
-
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
Display MoreOption 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
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.
-
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
-
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:
QuoteGive a description of the whole requirement and not a bit at a time as this will yield much better results
-
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. -
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
Display MorePrivate 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
(TV Enter event handler)
Code
Display MorePrivate 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
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.
-
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 -
Re: SearchBox for Treeview
To 'Click' the TV, replace the Textboxc8_KeyUp event hadler with:
Code
Display MorePrivate 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
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...
-
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? -
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...
-
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. -
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.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!