How to write the codes?

[maryam]

we have a listbox populated with A,F, C,k etc.

Also There is an excel sheet like this:
A D B F .....
10 20 30 40 .....

suppose A D B F can be from B1 to IV1 ( B1 is populated with A and C1 with D and so on)
we want to serach and say from B1 to IV1 ,if listbox.list = Cell Caption then return the value related to listbox.text.
So for example at E1, F will be found and 40 should be returned as F is in the listbox.
How to write the codes for this?

[lucas]

I'm sorry.....I read this 4 times and I still don't understand.

we want to serach and say from B1 to IV1 ,if listbox.list = Cell Caption
You wish to populate the listbox from row 1?..and if ...then? Maybe you could upload a small sample of the data and clear up what your trying to do please. Put some notes in the example file. The more detail you can give the easier it will be to understand what your trying to do.

[maryam]

Dear Lucas,
thank you for reading. List box is already populated with some items lets say A, F, C, K, etc.
We have these items in the fist row of excel sheet as well (from B1 to IV1) but the sequence is not the same. It may be A, D, B, F.
There is another row in the excel sheet in which we have the corresponding values (let say value 10 for A).
We want to return 10, 30, , ,... which are the values for A,F,C,K,etc (the items of listbox) Is it clear now?

[lucas]

Ok, I'm starting to understand. Question, is this sheet just for the data we are talking about here? In other words could row 1 be transposed to column A......then you could use it for the rowsource for the listbox and if the numbers were in column B they would be easy to return to a label.caption....

[maryam]

the sequence of items in row one is not the same as listbox and also we do not have all the items of row one in the listbox. row one is not a datasource for the listbox. In the listbox we have some items, in the excel sheet we have all data, and we want to find the values of the items used in the list box.

[lucas]

Is all of the data in 2 rows...Row A with the letters and Row B with the corresponding numbers?

[maryam]

yes

[Bob Phillips]

On Error Resume Next
iCol = Application.Match(Listbox1.Value, Worksheets("Sheet1").Range("B1:IV1"),0)
On Error Goto 0
If iCol> 0 Then
Msgbox Worksheets("Sheet1").Cells(2, iCol)
End If

[maryam]

with listbox.value, the item should be selected (in blue) I want it for all the items. What should I use alternatively?

[Bob Phillips]

Don't understand.

[maryam]

I mean if the item from listbox1 is selected ( Blue highlights), it will show the msg box, but if the item is not selected, it doesn't work.
I want to check for all the items in the listbox.

[maryam]

pls look at the attached file. It should give 1,4( but msg box gives nothing and 3)
cmdbottom doesn't work unless I select a or d in the listbox. I dont want the user to select, so should I change value to somthing else in
iCol = Application.Match(Listbox1.Value, Worksheets("Sheet1").Range("B1:IV1"),0)



Thanks and Regards,
Maryam

[malik641]

Took a quick look and the problem with iCol is that the Match() function returns a relative value of an array. In this case, the array is B1:IV1, so if B1 = a, then iCol = 1 because it's the first item in the array. This should be increased by 1 because when you use Cells(2, iCol) it returns the wrong value (i.e a="" and d=3, which weren't correct).

Here's a modified version of what you gave. Notice that I placed the "a, b, c, ..." values in Sheet2 from A1:A6, and because of that also notice the listbox RowSource (in VBE, click the ListBox and look in the properties for RowSource) says "Sheet2!A1:A6" so it shows all the values.

I added 1 to iCol to make the Cells(2, iCol) return the correct (or what I believe to be correct). Take a look.

Also:

...I dont want the user to select...What do you mean by this?

[maryam]

Thank you. I mean now the user should first click the listbox and select the item, then only the subrutine works. I dont want this. I want the rutine to be done for all the items of the list box.
try clicking on cmdbottom without selecting anything in the listbox( when no row is blue).

[malik641]

I want the rutine to be done for all the items of the list box.
try clicking on cmdbottom without selecting anything in the listbox( when no row is blue).
Ok. So what do you need to happen exactly, to return a sum of all the numbers? An average? A list of all the numbers? Please specify.

[maryam]

No. I want the number itself, I dont want the user to select any item of the listbox and I want the click event of the cmdbottom to return the numbers for all the items of the listbox (which is found from the worksheet by matching) in the third row of the worksheet for example again.

[maryam]

lets say we want to make it like this. Put the numbers in the text boxes.

[malik641]

No. I want the number itself, I dont want the user to select any item of the listbox and I want the click event of the cmdbottom to return the numbers for all the items of the listbox (which is found from the worksheet by matching) in the third row of the worksheet for example again. To be honest, I'm not sure what you are trying to show with your example. Do you want something like this to happen? Check out my example. If you do not select anything, a string of all the matching numbers comes up, and if you select something from the listbox, that one single matching number will show up.


What is the goal of what you are trying to do? In FULL DETAIL. Don't leave any step out, just pour it all out please :)

I also commented most of the code, to show what is going on :thumb

[maryam]

yes user does not select anything, aand I want the coressponding values of all the items of the list box in seprate text boxes.

[maryam]

​
For i1 = 1 To ListBox1.ListCount
Textbox2(i1).Text = ""
Next i1
For i = 1 To ListBox1.ListCount
Unload Textbox2(i)
Next i

I want to upload the same number of textboxes as the number of items in the list box and fill them with the coresponding numbers. Instead of manipulate it with the string of all numbers together.

[Bob Phillips]

For i1 = 1 To ListBox1.ListCount
Controls("TextBox" & (i1)).Text = ""
Next i1

[maryam]

It gives debug that cannot find the specified object and
Controls("TextBox" & (i1)).Text = ""
is yellow.

[Bob Phillips]

That is probably because there are not that many tetxboxes.

What exactly are you trying to do?

[maryam]

look at the last excel file Malik sent to me. I want to do the same, but to save the data in seprate textboxes instead of an string in one textbox.

I want to have the same number of textboxes as the number of the items in the listbox and then manupulate them.

[malik641]

A strange request...but here you go!

Using Bob's "Controls("TextBox" & i1)" method (thanks Bob):


Private Sub CommandButton1_Click()
Dim strTemp As String
Dim i As Long, iTemp As Long
' First, CLEAR ALL TEXTBOXES
With Me
.TextBox1.Value = ""
.TextBox2.Value = ""
.TextBox3.Value = ""
.TextBox4.Value = ""
End With
' Check if no items were selected
If ListBox1.ListIndex = -1 Then ' No items selected
' Collect all the values
' For this, we'll use the UsedRange property, as
' the data on Sheet1 is all the data we need
With Worksheets("Sheet1").UsedRange
For i = 1 To ListBox1.ListCount
' Find column to match
' First check if the item in the listbox has any value in it
If ListBox1.List(i - 1) <> "" Then
' NOTICE that I don't add 1 to the iTemp anymore, this is because when we use the UsedRange object, the very beginning _
' of the data is thought to be Cells(1, 1) even though on the sheet it may be Worksheets("Sheet1").Cells(1, 2)
iTemp = Application.WorksheetFunction _
.Match(ListBox1.List(i - 1), Worksheets("Sheet1").Range("B1:IV1"), 0)
Me.Controls("TextBox" & i).Value = iTemp
'\\ Store value
'\\strTemp = strTemp & .Cells(2, iTemp).Value & ", "
End If
Next
' Take out the last comma and space in strTemp
' strTemp = Strings.Left(strTemp, Strings.Len(strTemp) - 2)
' Place string into textbox
' TextBox1.Value = strTemp
End With
' Now exit the procedure
Exit Sub
End If
' If item selected, find which number corresponds
On Error Resume Next
iCol = Application.WorksheetFunction.Match(ListBox1.Value, Worksheets("Sheet1").Range("B1:IV1"), 0) + 1
On Error GoTo 0
' If something is found, place it in the textbox
If iCol > 1 Then
Me.Controls("TextBox" & (Me.ListBox1.ListIndex + 1)).Value = Worksheets("Sheet1").Cells(2, iCol)
End If
End Sub

[maryam]

It gives Debug " unable to get the match property of the WorksheetFunction Class " and iTemp = Application.WorksheetFunction _
.Match(ListBox1.List(i - 1), Worksheets("Sheet1").Range("B1:IV1"), 0) is highlighted.

[Bob Phillips]

Match doesn't work on multiple columns, just one column or one row.

[maryam]

we also want to match between the first row of sheet1 and the listbox items (Rowsource:Sheet2!A1:A4)

[malik641]

It is working with one row.


And maryam, you probably have a value that is not found on the worksheet. That may be the cause of your error. My example works fine, you must have modified it.

[maryam]

yes, sorry the problem was that I started the data of sheet1 from the first column.It should be from B1(Second column). Thank you.