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]

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

[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]

Your code colors the cells for every item in the ListBox no matter what item was selected. The way the OP wrote his descriptions, if you are right and he wants to color matching cells, I am kind of thinking he would only want to highlight cells with the selected item. If I am correct on this, then your code could do it by just removing the outer For..Next (the one incrementing the "i" variable).

[table="width: 500"]
[tr]
	[td]Private Sub CommandButton1_Click()
  'Modified 3-25-18 3:30 PM EDT
  Dim C As Range
  For Each C In ActiveSheet.UsedRange
    If C.Value = ListBox1.Value Then C.Interior.ColorIndex = 3
  Next
End Sub[/td]
[/tr]
[/table]

With that said, you can also do what your code does without using a loop at all...

[table="width: 500"]
[tr]
	[td]Private Sub CommandButton1_Click()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 3
  Cells.Replace ListBox1.Value, "", xlWhole, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[My Aswer Is This]

The original post said this:

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? ​

 

[Rick Rothstein]

The original post said this:

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? ​

True (I forgot about that), but he also said "No, just looking to see if the word "Lunch" is in the textbox then do this or that accordingly" in Message #8 (where I assumed he meant ListBox) which sounds like he is looking for single items at a time. I guess we are going to have to wait for the OP to come back to this thread in order to clear things up.

 

[zrx1200]

True (I forgot about that), but he also said "No, just looking to see if the word "Lunch" is in the textbox then do this or that accordingly" in Message #8 (where I assumed he meant ListBox) which sounds like he is looking for single items at a time. I guess we are going to have to wait for the OP to come back to this thread in order to clear things up.

Hello again Gents,

Yes I mean list box, and yes just looking for the word "Lunch" in this list box and it will be the only item in this list box. I just need to know if it is or is not present in the list box.

Rick, your code errors out on the function as the list box =null even with "Lunch" present. Type mismatch.

 

[Rick Rothstein]

Yes I mean list box, and yes just looking for the word "Lunch" in this list box and it will be the only item in this list box. I just need to know if it is or is not present in the list box.

Rick, your code errors out on the function as the list box =null even with "Lunch" present. Type mismatch.

You mean the code I posted in Message #9 ? Are you passing the exact name of your ListBox (not the example name I posted)?

What do you mean by "as the list box = null"?

 

[My Aswer Is This]

So you are going to put the word Lunch in the listbox and it's the only word in the listbox.
And you want the script to look and see if Lunch is in the listbox

If you already put Lunch in the listbox when would it never be in the listbox.

 

[zrx1200]

You mean the code I posted in Message [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9]#9 [/URL] ? Are you passing the exact name of your ListBox (not the example name I posted)?

What do you mean by "as the list box = null"?

Yes, post #9

I set if up with this:

Dim cMyListbox As msforms.ListBox
If OptionButton3 = True Then Set cMyListbox = UserForm1.ListBox11

cMyListbox is placed in your snippet for listbox.

Error 13 type mismatch

last line in function

IsInLB = InStr(1, Join(Application.Transpose(Arr), Chr(1)), Item, vbTextCompare)

so if I highlight the variable cMyListbox it = null

 

[zrx1200]

So you are going to put the word Lunch in the listbox and it's the only word in the listbox.
And you want the script to look and see if Lunch is in the listbox

If you already put Lunch in the listbox when would it never be in the listbox.

I clear it out in other code via command button. And yes your explanation is correct.

The purpose of this exercise is to allow me to exit other command button routines if "lunch" is present (not run code).

 

[My Aswer Is This]

Well now I guess that answers our question
I will just assume Rick will give you a answer.

 

[zrx1200]

Well now I guess that answers our question
I will just assume Rick will give you a answer.

Hmmm, not sure but did I miss something?