PDA

View Full Version : [SOLVED] Goto record using listbox



gibbo1715
01-25-2005, 09:01 AM
I have a dynamic range that is displayed in a list box.

Can I select an item in the list and when i click a button close the listbox and have the active row as per my selection?

I am using the code below re the list box


Dim Rng As Range
Me.ListBox1.Clear
With ThisWorkbook.Sheets("Actions")
Set Rng = .Range("A1", .Range("A65536").End(xlUp))
End With
For Each cell In Rng.Cells
With Me.ListBox1
.AddItem cell.Offset(0, 2).Value
.List(.ListCount - 1, 1) = cell.Value
'.List(.ListCount - 1, 1) = cell.Offset(0, 3).Value
End With
Next cell

Many thanks

Jacob Hilderbrand
01-25-2005, 09:09 AM
What we can do is record the row number to a second listbox. Just set the second listbox's Visible property to False.



Option Explicit

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

Private Sub UserForm_Initialize()
Dim Rng As Range
Dim Cell As Range
Me.ListBox1.Clear
With ThisWorkbook.Sheets("Actions")
Set Rng = .Range("A1", .Range("A65536").End(xlUp))
End With
For Each Cell In Rng.Cells
Me.ListBox1.AddItem Cell.Offset(0, 2).Value
Me.ListBox2.AddItem Cell.Row
Next Cell
End Sub

gibbo1715
01-25-2005, 11:26 AM
works great thank you

Jacob Hilderbrand
01-25-2005, 05:42 PM
You're Welcome :beerchug:

Take Care