Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

use split function to seperate values in textbox specific delimiter

vba 2010

previous question for reference:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28120329.html



Userform:
 textbox
listbox


currently this code is taking the value in a textbox and if it does not exist in the loistbox in column 4 it deletes it from the listbox.




' the code is working fine...

What I need: I now want to use the Split function and using a ;(semi-colon) as a delimiter.
In the textbox...search for each value from the textbox getting rid of all the items in the listbox that do not contain the values from the textbox..

i.e. Textbox33 may have:

4-40;Hex;1/2;Steel

So all 4 criteria must exist somewhere in the column specified





 
Dim strFilter As String
Dim lngIndex As Long

strFilter = frmResultAll.TextBox33.Text

For lngIndex = ListBox32.ListCount - 1 To 0 Step -1
    If InStr(1, ListBox32.List(lngIndex, 3), strFilter) = 0 Then
        ListBox32.RemoveItem (lngIndex)
    End If
Next


Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 7
  • 7
1 Solution
 
Martin LissRetired ProgrammerCommented:
Private Sub CommandButton1_Click()
Dim strFilter() As String
Dim lngIndex As Long
Dim lngPart As Long
Dim bFound As Boolean

strFilter = Split(TextBox33.Text, ";")

For lngIndex = ListBox1.ListCount - 1 To 0 Step -1
    bFound = False
    For lngPart = 0 To UBound(strFilter)
        If ListBox1.List(lngIndex, 3) = strFilter(lngPart) Then
            bFound = True
            Exit For
        End If
    Next
    If Not bFound Then
        ListBox1.RemoveItem (lngIndex)
    End If
Next

End Sub

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
That search is case sensitive. If you don't want it to be then change line 12 to

        If UCase(ListBox1.List(lngIndex, 3)) = UCase(strFilter(lngPart)) Then
0
 
FordraidersAuthor Commented:
Martinless , If i put 1 value or 2 or more values in textbox33...it wipes out the whole data in listbox ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
FordraidersAuthor Commented:
and i may only have one value sometimes.  sorry for the problems
0
 
Martin LissRetired ProgrammerCommented:
I assumed that if the listbox item does not contain 4-40 or Hex or 1/2 or Steel (or any other values that might be in the textbox) then that listitem should be deleted. Since that's apparently not what you want, please describe what should happen.
0
 
FordraidersAuthor Commented:
the other code was working fine..
"I assumed that if the listbox item does not contain 4-40 or Hex or 1/2 or Steel (or any other values that might be in the textbox) then that listitem should be deleted"
Yes this is what i want..

The listbox rows left would be rows that contain
4-40 or Hex or 1/2 or Steel

Thanks
0
 
Martin LissRetired ProgrammerCommented:
Martinless , If i put 1 value or 2 or more values in textbox33...it wipes out the whole data in listbox ?
I assume that you are describing a problem. If so please give me more detail.
0
 
FordraidersAuthor Commented:
no matter what i type in  textbox33   it will not filter correctly...
I see 4-40 in several rows in my listbox so it should retain those rows but deletes everyting.
0
 
Martin LissRetired ProgrammerCommented:
Here is my version. Does it do what you want?
listbox.xlsm
0
 
FordraidersAuthor Commented:
o, I see the communication problem.

I have descriptions.

Hex, 4-40 1/2-30 x Box Hex head
Hex, 5.20 1/2-20 x Square Head
Hex, 4-40 1/2-30 x Square head

so it want to filter
4-40;Square

I should only see
Hex, 4-40 1/2-30 x Square head

so it want to filter
4-40;Hex;Head

Hex, 4-40 1/2-30 x Box Hex head
Hex, 4-40 1/2-30 x Square head
0
 
Martin LissRetired ProgrammerCommented:
OK try this.


Private Sub CommandButton1_Click()
Dim strFilter() As String
Dim lngIndex As Long
Dim lngPart As Long
Dim bFound As Boolean

strFilter = Split(TextBox33.Text, ";")

For lngIndex = ListBox1.ListCount - 1 To 0 Step -1
    bFound = True
    For lngPart = 0 To UBound(strFilter)
        If InStr(1, UCase(ListBox1.List(lngIndex, 3)), UCase(strFilter(lngPart))) = 0 Then
            bFound = False
            Exit For
        End If
    Next
    If Not bFound Then
        ListBox1.RemoveItem (lngIndex)
    End If
Next

End Sub

Open in new window

0
 
FordraidersAuthor Commented:
Thanks very much working fine.
0
 
FordraidersAuthor Commented:
Thanks
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and again I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now