none
Searching a treeview RRS feed

  • Question

  • Hi All,

    I have a treeview placed on an Excel userform.  I currently search the treeview for text that is enter in a textbox on that userform.  My code searches the treeview and if the text is found it selects the node in which it finds.  If the text is not found, it pops up a msgbox saying 'Sorry, text not found.'

    Unfortunately, each time I search the treeview it begins searching from the top.  This is a problem because I'm unable to find multiple nodes with the same search criteria.

    What I would like to do is search treeview and find the first instance, then continue searching from that point finding the next instance.  If the text is note found again it can display a message saying that's it, or loop around and search from the top again.  

    Hopefully someone is able to assist.

    Sub FindNode()
      Dim fText
      Dim i As Long
      UserForm2.TreeView1.SetFocus
      fText = UserForm2.TextBox1.Text
      For i = 1 To UserForm2.TreeView1.Nodes.Count
        If InStr(LCase(UserForm2.TreeView1.Nodes.Item(i).Text), LCase(fText)) > 0 Then
          UserForm2.TreeView1.Nodes.Item(i).Selected = True
          Exit Sub
        End If
      Next i
      MsgBox ("Sorry, text not found.")
    End Sub
    
     

    Cheers

    Brad

    Tuesday, February 16, 2016 3:03 PM

Answers

  • Try this version. It uses a static variable to keep track of the current position:

    Sub FindNode()
      Static Start As Long
      Dim fText
      Dim i As Long
      UserForm2.TreeView1.SetFocus
      fText = UserForm2.TextBox1.Text
      For i = Start + 1 To UserForm2.TreeView1.Nodes.Count
        If InStr(LCase(UserForm2.TreeView1.Nodes.Item(i).Text), LCase(fText)) > 0 Then
          UserForm2.TreeView1.Nodes.Item(i).Selected = True
          Start = i
          Exit Sub
        End If
      Next i
      MsgBox "Sorry, text not found."
      ' Reset Start
      Start = 0
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by _Brad_C_ Tuesday, February 16, 2016 10:47 PM
    Tuesday, February 16, 2016 3:12 PM

All replies

  • Try this version. It uses a static variable to keep track of the current position:

    Sub FindNode()
      Static Start As Long
      Dim fText
      Dim i As Long
      UserForm2.TreeView1.SetFocus
      fText = UserForm2.TextBox1.Text
      For i = Start + 1 To UserForm2.TreeView1.Nodes.Count
        If InStr(LCase(UserForm2.TreeView1.Nodes.Item(i).Text), LCase(fText)) > 0 Then
          UserForm2.TreeView1.Nodes.Item(i).Selected = True
          Start = i
          Exit Sub
        End If
      Next i
      MsgBox "Sorry, text not found."
      ' Reset Start
      Start = 0
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by _Brad_C_ Tuesday, February 16, 2016 10:47 PM
    Tuesday, February 16, 2016 3:12 PM
  • Thanks Hans,

    Perfect!

    Are you able to tell how I'd search for nodes with the exact text entered? 

    Cheers

    Brad


    • Edited by _Brad_C_ Tuesday, February 16, 2016 10:50 PM
    Tuesday, February 16, 2016 10:47 PM
  • Change the line

        If InStr(LCase(UserForm2.TreeView1.Nodes.Item(i).Text), LCase(fText)) > 0 Then

    to

        If LCase(UserForm2.TreeView1.Nodes.Item(i).Text) = LCase(fText) Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Wednesday, February 17, 2016 7:16 AM
    • Unproposed as answer by David_JunFeng Wednesday, February 17, 2016 7:30 AM
    Wednesday, February 17, 2016 6:50 AM
  • >>>Are you able to tell how I'd search for nodes with the exact text entered?

    According to your description, I suggest that you could use the StrComp function to return the results of a string comparison, refer to below code:

    Dim MyStr1, MyStr2, MyComp
    MyStr1 = "ABCD": MyStr2 = "abcd"       ' Define variables.
    MyComp = StrComp(MyStr1, MyStr2, 1)    ' Returns 0.
    MyComp = StrComp(MyStr1, MyStr2, 0)    ' Returns -1.
    MyComp = StrComp(MyStr2, MyStr1)       ' Returns 1.

    For more information, click here to refer about StrComp Function

    Wednesday, February 17, 2016 7:37 AM