Viewing listbox items problem

[gibbo1715]

xld gave me the great code below for filtering my data from a spreadsheet into a listbox


Dim LastRow As Long
Dim i As Long
Dim oLB As Object
Set oLB = frmItems.ListBox1
With ThisWorkbook.Sheets("Items")
If Range("A3").Value = "" Then
LastRow = Range("3")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End If
For i = 3 To LastRow
If Cells(i, "F").Value = "y" Then
oLB.AddItem Cells(i, "A")
oLB.List(oLB.ListCount - 1, 1) = Cells(i, "B")
oLB.List(oLB.ListCount - 1, 2) = Cells(i, "C")
oLB.List(oLB.ListCount - 1, 3) = Cells(i, "D")
End If
Next i
End With

I was using the code below to be able to click on the item in the list box to view it


If Me.ListBox1.Text <> "" Then
Range(ListBox1.RowSource).Offset(ListBox1.ListIndex, 0).Cells(1, 1).Select
End If

Now my list is fitered this no longer works, is there an easy way to make it so when i click the entry in my listbox i display that record?

cheers

gibbo

[Bob Phillips]

Get it from the listbox, not the original source



With Me.ListBox1
If .Text <> "" Then
MsgBox .Text
End If
End With

[Norie]

I'm afraid since you are filtering out items for your list that simple method won't work anymore.

It relies on the data being in contiguous rows which correspond to the ListIndex of the listbox.

What you need to do know is somehow add the row for each item somewhere.

You could add it as an extra (hidden) column on the listbox.

You would then reference the 2nd column to get the row when an item is selected.

[gibbo1715]

My problem is i need to then write data back to the same location in the worksheet if thats possible?

[Bob Phillips]

Don't understand why you want to write back what is already there, but easiest way is to add an extra column as Norie suggests.



Dim LastRow As Long
Dim i As Long
Dim oLB As Object
Set oLB = frmItems.ListBox1
With ThisWorkbook.Sheets("Items")
If Range("A3").Value = "" Then
LastRow = Range("3")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End If
For i = 3 To LastRow
If Cells(i, "F").Value = "y" Then
oLB.AddItem Cells(i, "A")
oLB.List(oLB.ListCount - 1, 1) = Cells(i, "B")
oLB.List(oLB.ListCount - 1, 2) = Cells(i, "C")
oLB.List(oLB.ListCount - 1, 3) = Cells(i, "D")
oLB.List(oLB.ListCount - 1, 4) = i
End If
Next i
End With


no need to add a column in the listbox properties, then it will stay hidden.

To write it back, use



With Me.ListBox1
Cells(.List(.ListIndex, 4), 1).Value = .Value
End With

[gibbo1715]

Ok Hadnt thought of that, the code below will do that ( I Think)


Dim LastRow As Long
Dim i As Long
Dim oLB As Object
Sheets("Item").Select
Range("A3").Select
Label1.Caption = Sheets("Item").Range("A2").value
Label2.Caption = Sheets("Item").Range("B2").value
Label3.Caption = Sheets("Item").Range("C2").value
Label4.Caption = Sheets("Item").Range("D2").value
On Error Resume Next
Me.ListBox1.Clear
Me.ListBox2.Clear
Sheets("Item").Select
On Error Resume Next
frmExhibits.ListBox1.Clear
frmExhibits.ListBox2.Clear
Set oLB = frmExhibits.ListBox1
With ThisWorkbook.Sheets("Item")
If Range("A3").value = "" Then
LastRow = Range("3")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End If
For i = 3 To LastRow
If Cells(i, "F").value = "y" Then
oLB.AddItem Cells(i, "A")
oLB.List(oLB.ListCount - 1, 1) = Cells(i, "B")
oLB.List(oLB.ListCount - 1, 2) = Cells(i, "C")
oLB.List(oLB.ListCount - 1, 3) = Cells(i, "D")
Me.ListBox2.AddItem i 'Cells.Row
End If
Next i
End With


Now how to i make the cell in column a active when i select it?


If Me.ListBox1.Text <> "" Then
Range("A" & Me.ListBox2.List(Me.ListBox1.ListIndex)).Select
End If

will not work for me:banghead:

[gibbo1715]

sorry posted before i saw xld's reply

[gibbo1715]

doesnt work for me, i need to make the correct record active (column A) on the worksheet, the line number is displayed but doesnt change the active cell when i click on it

[gibbo1715]

xld gave me this really good code


Don't understand why you want to write back what is already there, but easiest way is to add an extra column as Norie suggests.





Dim LastRow As Long
Dim i As Long
Dim oLB As Object
Set oLB = frmItems.ListBox1
With ThisWorkbook.Sheets("Items")
If Range("A3").Value = "" Then
LastRow = Range("3")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End If
For i = 3 To LastRow
If Cells(i, "F").Value = "y" Then
oLB.AddItem Cells(i, "A")
oLB.List(oLB.ListCount - 1, 1) = Cells(i, "B")
oLB.List(oLB.ListCount - 1, 2) = Cells(i, "C")
oLB.List(oLB.ListCount - 1, 3) = Cells(i, "D")
oLB.List(oLB.ListCount - 1, 4) = i
End If
Next i
End With



no need to add a column in the listbox properties, then it will stay hidden.


To write it back, use




With Me.ListBox1 Cells(.List(.ListIndex, 4), 1).Value = .Value
End With








Can anyone tell me why


With Me.ListBox1
Cells(.List(.ListIndex, 4), 1).Value = .Value
End With

Does not select the cell A on the relivent row on my worksheet Please?

[Bob Phillips]

Do you want to set the value


With Me.ListBox1
Cells(.List(.ListIndex, 4), 1).Value = .Value


or select that row


With Me.ListBox1
Cells(.List(.ListIndex, 4), 1).Select
End With

[gibbo1715]

Thats what i was after, thank you very much

Solved Again

Gibbo