List box selection to a number of tboxes

[dubiousp]

Hello all,, please forgive me I posted a similar question a couple of days ago but did not explain very well

Im trying for our local charity to write a medic logging VBA Excel,, one of the user forms is to log up eight drugs (ufmedication) the form consists of a listbox (lbdrugs) sourced from a column list,, 8 text boxes (tbmed1 to tbmed) in addition there is a text box for searching (tbmedsearch)

My preferred operation was is that i would start to enter text into tbmed1 and then select from list to move to tbmed2 ect. If this was not possible going to use search box....

However I have two codes one that is the lbdugs look up and one that is the tb selection

this code seems to run then halts at tbmed2.value

Private Sub lbdrugs_*******()



ClearControls
If Me.lbdrugs.ListIndex = -1 Then 'not selected
'MsgBox " No selection made"
ElseIf Me.lbdrugs.ListIndex >= 1 Then 'User has selected
r = Me.lbdrugs.ListIndex
With Me
.tbmed1.Value = lbdrugs.List(r, 0)
'.tbmed2.SetFocus
.tbmed2.Value = lbdrugs.List(r, 1)

'etc
'End With
End If
End Sub


The below is my lbdrugs search code
Private Sub tbmedsearch_Change()






Dim i As Long
Dim sFind As String

sFind = Me.tbmedsearch.Text

If Len(sFind) = 0 Then
Me.lbdrugs.ListIndex = -1
Me.lbdrugs.TopIndex = 0
Else
For i = 0 To Me.lbdrugs.ListCount - 1
If UCase(Left(Me.lbdrugs.List(i), Len(sFind))) = UCase(sFind) Then
Me.lbdrugs.TopIndex = i
Me.lbdrugs.ListIndex = i

Exit For
End If
Next i
End If
End Sub