CODEWITHSUNDEEP
vbaexcellistbox
Doolie1106
Doolie1106

Reputation: 309

find a row # of the selected item in listbox vba

How can i find the row # of selected item in listbox?

right now i have a sub that inputs all of the found item into the listbox, see below

Sub findnext()
Dim Name As String
Dim f As Range
Dim ws As Worksheet
Dim s As Integer
Dim findnext As Range

Set ws = ThisWorkbook.Worksheets("Master")
Name = surname.Value
ListBox1.Clear
Set f = Range("A:A").Find(what:=Name, LookIn:=xlValues)
Set findnext = f

Do
 Debug.Print findnext.Address
 Set findnext = Range("A:A").findnext(findnext)

 ListBox1.AddItem findnext.Value
 ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Cells(findnext.Row, xFirstName).Value
 ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Cells(findnext.Row, xTitle).Value
 ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Cells(findnext.Row, xProgramAreas).Value
 ListBox1.List(ListBox1.ListCount - 1, 4) = ws.Cells(findnext.Row, xEmail).Value
 ListBox1.List(ListBox1.ListCount - 1, 5) = ws.Cells(findnext.Row, xStakeholder).Value
 ListBox1.List(ListBox1.ListCount - 1, 6) = ws.Cells(findnext.Row, xofficephone).Value
 ListBox1.List(ListBox1.ListCount - 1, 7) = ws.Cells(findnext.Row, xcellphone).Value
'ListBox1.List(ListBox1.ListCount - 1, 8) = ws.Cells(findnext.Row, xFirstName).Value
Loop While findnext.Address <> f.Address

End Sub

and if the user selects the item in the listbox, then it'll populate the information into the textbox of the userform

Sub ListBox1_Click()

    surname.Value = ListBox1.List(ListBox1.ListIndex, 0)
    firstname.Value = ListBox1.List(ListBox1.ListIndex, 1)
    tod.Value = ListBox1.List(ListBox1.ListIndex, 2)
    program.Value = ListBox1.List(ListBox1.ListIndex, 3)
    email.Value = ListBox1.List(ListBox1.ListIndex, 4)
    SetCheckBoxes ListBox1.List(ListBox1.ListIndex, 5)        '<<<< added
    officenumber.Value = ListBox1.List(ListBox1.ListIndex, 6)
    cellnumber.Value = ListBox1.List(ListBox1.ListIndex, 7)     

End Sub

i want to figure out from ListBox1_click() how i can determine the row # of the selected item in the listbox. once i figure this out, i'll code an update sub where it'll locate the row # of the selected item and i'll re-enter the information in the textboxes and update the information of the row.

i thought about storing a row # in a hidden worksheet when i do find.. but i don't know how to link the row # of the found with what's been selected in the listbox

hopefully...this makes sense, if not please let me know!

Upvotes: 1

Views: 32417

Answers (2)

Doolie1106
Doolie1106

Reputation: 309

I've found a way to go around this - i've made an extra listcolumn to store the row# of the found when the item's been searched. then i made another textbox within the userform that would input the row#:

therefore, within the findnext() sub i added the following line storerownumber = findnext.row and added another line for listbox. `listbox1.list(listbox1.listcount-1,8) = storerownumber

and within the listbox1_click() sub i've added the following line: rownumber.value = listbox1.list(listbox1.listindex,8)

and made a new sub update_click() and added the following lines:

Dim r As Long
Dim update As Range
Dim ws As Worksheet

Set ws = Worksheets("Master")
rownumber.Value = ListBox1.List(ListBox1.ListIndex, 8)
r = rownumber

ws.Cells(r, xLastName).Value = surname.Value
ws.Cells(r, xFirstName).Value = firstname.Value
ws.Cells(r, xTitle).Value = tod.Value
ws.Cells(r, xProgramAreas).Value = program.Value
ws.Cells(r, xEmail).Value = email.Value
ws.Cells(r, xStakeholder).Value = GetCheckBoxes
ws.Cells(r, xofficephone).Value = officenumber.Value
ws.Cells(r, xcellphone).Value = cellnumber.Value

end sub

and it works fine!

Upvotes: 3

PatricK
PatricK

Reputation: 6433

As far as I know, you have to loop through all rows in ListBox since you can have Multi-Select.

For r = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(r) Then
        Debug.Print "You selected row #" & r + 1
    End If
Next

Upvotes: 1

Related Questions