Making A Listbox Select A Cell.

hi,

i'm working on a userform. it has a listbox. when the form starts up, it looks in column A for any cells that have something in it (starting after row 2).

for each cell that has something in it, a line is added to the listbox. the line added in the listbox is whatever the value of the cell and the cell one column right of it was.

here's the code:

Private Sub UserForm_Initialize()

With Range("A:A")
    For Each r In Range("A:A")
    If Not r.Value = 0 And r.Row > 2 Then
    ListBox1.AddItem r.Value & " " & r.Offset(0, 1).Value, -1
    End If
    Next
End With
    
    ListBox1.ListIndex = ListBox1.ListCount - 1
    
End Sub

now, i'm trying work on code that will make it so that when i click on a line in the listbox, the corresponding cell in row A will be selected. but i don't know how to do this. any ideas?

thankyou

Add this click event code to your userform

Private Sub ListBox1_Click()
    For x = 0 To ListBox1.ListCount
        
        If ListBox1.Selected(x) = True Then 
            Cells(x + 3, 1).Select
        End If
    
    Next x
End Sub

Right now it only works correctly on contiguous data in A but I'm working on getting it to recognize skipped cells to deal with non contiguous data.

Hope this helps

OK, I figured out a better way which ignores blank entries in column A
use this in the listbox click event rather than the code I posted earlier

Private Sub ListBox1_Click()
    For x = 0 To Cells(Rows.Count, "A").End(xlUp)
      If ListBox1.Value = Cells(x + 3, 1) & " " & Cells(x + 3, 2) Then
        Cells(x + 3, 1).Select
      End If
    Next x
 End Sub

Hope this helps

hey i'm looking for the same... but i want to select multiple cells with the option multiselect...

can you help me???

mister190

Read forum rules before you post a comment, Start a new post for your query.

Marc