How do you use a value selected from a combo box on a userform to search for that value in an excel worksheet?

[newbie_12]

Hi!

Could someone please show me how to you use a value selected from a combo box (Comboparticipant) on a userform to search for that value in an excel worksheet? Once I find that value on the worksheet I want to enter a new value (name of mentor) to the corresponding column. Please note that my combo box Comboparticipant has 3 columns which are student ID, first name & last name.

This is my code:

Private Sub cmdassignmentor_Click()
Dim lRow As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("Participant Details")




'find row matching the value selected in Me.Comboparticipant'
lRow = ws1.Cells.Find(What:="Me.Comboparticipant.value", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected


With ws1
' .Unprotect Password:="password"
.Cells(lRow, 4).Value = Me.Combomentor.Value
' .Protect Password:="password"
End With


'clear the data
Me.Comboparticipant = ""
Me.Combomentor.Value = ""
Me.Comboparticipant.SetFocus


End Sub




Private Sub UserForm_Initialize()
Dim cell_Participant As Range
Dim cell_Mentor As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Participant Lookup List")
Set ws2 = Worksheets("Facilitator Lookup List")


For Each cell_Mentor In ws2.Range("Facilitators")
With Me.Combomentor
.AddItem cell_Mentor.Value
End With
Next cell_Mentor

For Each cell_Participant In ws1.Range("StudentID")
With Me.Comboparticipant
.AddItem cell_Participant.Value
.List(.ListCount - 1, 1) = cell_Participant.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell_Participant.Offset(0, 2).Value
End With
Next cell_Participant
End Sub


Hope you can help me!

 

[newbie_12]

Hi! Could someone please show me how to you use a value selected from a combo box (Comboparticipant) on a userform to search for that value in an excel worksheet? Once I find that value on the worksheet I want to enter a new value (name of mentor) to the corresponding column. Please note that my combo box Comboparticipant has 3 columns which are student ID, first name & last name.

This is my VBA code:

Private Sub cmdassignmentor_Click()
Dim lRow As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("Participant Details")




'find row matching the value selected in Me.Comboparticipant'
lRow = ws1.Cells.Find(What:="Me.Comboparticipant.value", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected


With ws1
' .Unprotect Password:="password"
.Cells(lRow, 4).Value = Me.Combomentor.Value
' .Protect Password:="password"
End With


'clear the data
Me.Comboparticipant = ""
Me.Combomentor.Value = ""
Me.Comboparticipant.SetFocus


End Sub




Private Sub UserForm_Initialize()
Dim cell_Participant As Range
Dim cell_Mentor As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Participant Lookup List")
Set ws2 = Worksheets("Facilitator Lookup List")


For Each cell_Mentor In ws2.Range("Facilitators")
With Me.Combomentor
.AddItem cell_Mentor.Value
End With
Next cell_Mentor

For Each cell_Participant In ws1.Range("StudentID")
With Me.Comboparticipant
.AddItem cell_Participant.Value
.List(.ListCount - 1, 1) = cell_Participant.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell_Participant.Offset(0, 2).Value
End With
Next cell_Participant
End Sub


Hope you can help me!

 

[Norie]

You should be able to use the ListIndex property of the combobox to find the row of the selected participant.

Something like this.

Dim idx As Long
Dim rw As Long

    idx = Comboparticipant.ListIndex

    If idx <> -1 Then
        rw = Worksheets("Participant Lookup List").Range("StudentID").Cells(idx+1,1).Row
    End If

 

[newbie_12]

I had to change ".Cells(idx+1,1) to Cells(idx + 4, 1) anf then it worked!!! Do you know why idx+1 did not return the right row?

If idx <> -1 Then
rw = Worksheets("Participant Lookup List").Range("StudentID").Cells(idx + 4, 1).Row
End If

 

[Norie]

Not really, but it's probably something to do with how you've defined your named ranges.

 

[newbie_12]

Thanks a lot for your help!