Testing an item selected from a list

[Hymie]

G'day,
I have the following data in a spreadsheet:

Book1
	A	B	C	D	E
1	Tom	Sydney	yes
2	Tom	Melbourne	yes
3	Tom	Adelaide	no
4	Jack	Perth	yes
5	Jack	Darwin	no
6	Harry	Brisbane	yes
7	Harry	Hobart	no
8	Harry	Canberra	yes
9
10
Sheet1

I have created a user form which has 2 list boxes. The first shows the names Tom, Jack & Harry, and the second list box shows the cities conditional on which name has been selected. That is, if I select Jack in the first list box then the cities Perth and Darwin are shown in the second list box. So far, so good.

What I want to do now (and don't know how to) is click on a city and if it has "no" associated with it from Column C then I want to display a Message Box saying "No Way" or whatever I put in it, but if it has "yes" next to it then I just want the city selected and I will then move it to another list.

I guess what I am looking for is an understanding of how to use data in a row when one of the columns from a row contains an entry that is used as an item in a list box.

I hope this makes sense.

BTW, I have already read many forum entries relating to cascading list boxes etc, but I couldn't find anything that answered my question. However, if this question has already been answered I would like to apologise in advance and ask that you please just point me in the right direction for enlightenment.

Cheers, Hymie

 

[Tom Urtis]

Assuming your "second list box" is named ListBox2, this would do what you say you want, based on your screen shot of source data being in column B for that second listbox.

This code goes in your userform module.

Be sure to have the Option Explicit and Option Compare Text statements at the top of the module, each only once in the module, outside any and all procedures.


Option Explicit
Option Compare Text

Private Sub ListBox2_Click()
Dim strLB$, InfoLB As Variant
With ListBox2
strLB = .List(.ListIndex)
End With
InfoLB = Application.Match(strLB, Columns(2), 0)
If Not IsError(InfoLB) Then
If Cells(Cells((InfoLB), 2).Row, 3).Value = "No" Then
MsgBox _
"''No'' exists in cell " & _
Cells((InfoLB), 3).Address(0, 0) & _
" for " & strLB & ".", 48, "No means No."
Exit Sub
Else
MsgBox strLB & " was found in cell " & _
Cells((InfoLB), 2).Address(0, 0) & "," & vbCrLf & _
"and ''No'' does not exist in cell " & _
Cells((InfoLB), 3).Address(0, 0) & " !!", _
64, "Happy days !! It's not No !!"
End If
Else
MsgBox InfoLB & " was not found in column B.", _
16, "No such animal."
End If
End Sub

 

[Hymie]

Thanks for your help Tom. Your code does exactly what I was hoping to achieve. I just need to go through it and try to understand it!
Thanks, Hymie

 

[Tom Urtis]

Good that it works for you and you're wanting to learn a little about how it works.

Basically it assigns a string-type variable to the the selected ListBox item and compares it with the values in column B using the Match function. If a match is made, the Cells range syntax is invoked to see if "no" or "No" (thanks to the Option Compare Text statement) is in the corresponding cell of column C.

A message box lets you know one of 3 possible outcomes:

- A match was found but "no" or "No" is in column C.
- A match was found but "no" or "No" is not in column C.
- A match was not found.

The "Address(0, 0)" suffix is for readability convenience, to display the cell addresses as text in the message box without the distracting absolute dollar sign characters.