listbox value

[zrx1200]

I've been looking for a way in vba to look at value(s) of a list box and find if a certain one exists, NOT selected item. but rather if it is in the list box?

Any suggestions as this one seems to be a not well traveled road?

Ralph

 

[Rick Rothstein]

I've been looking for a way in vba to look at value(s) of a list box and find if a certain one exists, NOT selected item. but rather if it is in the list box?

Is the ListBox on a worksheet or on a UserForm?

If on a worksheet, is it a Forms ListBox or an ActiveX ListBox?

Are any of the ListBox items part of one of the other items in the ListBox? What I am asking here is could you have an item named "Manager" and another item named, say, "Office Manager" (where the first is part of the second)?

 

[Fluff]

Maybe something like

Private Sub CommandButton1_Click()
If UBound(Filter(Application.Transpose(Me.ListBox1.List), "Davy", True, vbTextCompare)) >= 0 Then MsgBox "found"
End Sub

 

[My Aswer Is This]

You did not say what to do when value was found.

This script will set the background color of the cell red when found.

Sub Search_For_ListBox_Item()
'Modified 3-25-18 2:30 PM EDT
Dim c As Range

For i = 0 To ActiveSheet.ListBox1.ListCount - 1
    For Each c In ActiveSheet.UsedRange
        If c.Value = ActiveSheet.ListBox1.List(i) Then c.Interior.ColorIndex = 3
    Next
Next
End Sub

 

[zrx1200]

QUOTE=Rick Rothstein;5037058]Is the ListBox on a worksheet or on a UserForm?

If on a worksheet, is it a Forms ListBox or an ActiveX ListBox?

Are any of the ListBox items part of one of the other items in the ListBox? What I am asking here is could you have an item named "Manager" and another item named, say, "Office Manager" (where the first is part of the second)?[/QUOTE]

On user form and and Im going to say ActiveX forget? How do you check lol.

List box 1 allows selection then are transferred over to list box 2

So, I would be looking for "manager" is list box and do something if found.

 

[zrx1200]

You did not say what to do when value was found.

This script will set the background color of the cell red when found.

Sub Search_For_ListBox_Item()
'Modified 3-25-18 2:30 PM EDT
Dim c As Range

For i = 0 To ActiveSheet.ListBox1.ListCount - 1
    For Each c In ActiveSheet.UsedRange
        If c.Value = ActiveSheet.ListBox1.List(i) Then c.Interior.ColorIndex = 3
    Next
Next
End Sub

Yes, I never said as I thought I could figure that portion out hopefully. LOL BTW its a form list box will see if I can mod your snippet.

 

[Rick Rothstein]

QUOTE=Rick Rothstein;5037058]So, I would be looking for "manager" is list box and do something if found.

I understand that... for the method I am thinking of, what I am asking is if there could be an accidental find of "Office Manager" while I am looking for "Manager". So, is it possible for your ListBox to have items whose text can be found embedded with other items in the ListBox?

 

[zrx1200]

I understand that... for the method I am thinking of, what I am asking is if there could be an accidental find of "Office Manager" while I am looking for "Manager". So, is it possible for your ListBox to have items whose text can be found embedded with other items in the ListBox?

AH, ok sorry. String search kind deal is why the question, Yes?

No, just looking to see if the word "Lunch" is in the textbox then do this or that accordingly.

Hope that clears up the air for you sir.

 

[Rick Rothstein]

No, just looking to see if the word "Lunch" is in the textbox then do this or that accordingly.

First off, you meant ListBox, not TextBox, correct?

Put the following function in your UserForm's code box and then call it as needed from within whatever control event procedure you want. Simly pass in the control name (no quotes around it) and the text (in quotes if you are hard-coding it) that you want to find and it will return True if that text is an item in the ListBox and False otherwise. Here is an example of using it...

If IsInLB(ListBox1, "Lunch") Then
  ....
Else
  ....
Endif

Here is the function code...

[table="width: 500"]
[tr]
	[td]Function IsInLB(ListBox As Control, Item As String) As Boolean
  Dim Arr As Variant
  Arr = ListBox.List
  IsInLB = InStr(1, Join(Application.Transpose(Arr), Chr(1)), Item, vbTextCompare)
End Function[/td]
[/tr]
[/table]

 

[My Aswer Is This]

Here is how my script would be written if listbox is in a UserForm.

Private Sub CommandButton12_Click()
'Modified 3-25-18 3:30 PM EDT
Dim c As Range

For i = 0 To ListBox1.ListCount - 1
    For Each c In ActiveSheet.UsedRange
        If c.Value = ListBox1.List(i) Then c.Interior.ColorIndex = 3
    Next
Next
End Sub