listbox value

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
76,157
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
19,069
Office Version
  1. 2013
Platform
  1. Windows
You did not say what to do when value was found.

This script will set the background color of the cell red when found.
Code:
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

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
You did not say what to do when value was found.

This script will set the background color of the cell red when found.
Code:
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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...
Code:
If IsInLB(ListBox1, "Lunch") Then
  ....
Else
  ....
Endif
Here is the function code...
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

Well-known Member
Joined
Jul 5, 2014
Messages
19,069
Office Version
  1. 2013
Platform
  1. Windows
Here is how my script would be written if listbox is in a UserForm.

Code:
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
 

Forum statistics

Threads
1,171,655
Messages
5,876,721
Members
433,207
Latest member
Bitt0101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top