Hi all,
I would appreciate some help (again)!
I would like to combine 2 functions.
I have a Search feature that updates a ListBox with possible matches as you type. I would like the following to happen when you dbl click a record in the ListBox: It populates the UserForm with the data of that specific row in your sheet.
Here is my code for the Search feature (works):
Private Sub TextBox1_Change()
Dim rng As Range, e
With Me
.ListBox3.Clear
If Len(.TextBox1.Value) Then
For Each e In Sheets("DataSheet - Fitness").Cells(1).CurrentRegion.Columns(1).Offset(1).Value
If (e <> "") * (e Like "*" & .TextBox1.Value & "*") Then
.ListBox3.AddItem e
End If
Next
With .ListBox3
If .ListCount > 0 Then .ListIndex = 0
End With
End If
End With
End Sub
Here is my code for dbl click of item in the ListBox that is to populate my UserForm (doesn't work):
Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim idx As Long
'idx = cmbDERS.ListIndex
If idx <> -1 Then
With Worksheets("DataSheet - Fitness")
GUID.Text = .Cells(idx + 2, -5).Value
txtCompany.Text = .Cells(idx + 2, -4).Value
txtDepartment.Text = .Cells(idx + 2, -3).Value
txtSite.Text = .Cells(idx + 2, -2).Value
txtDate.Text = .Cells(idx + 2, -1).Value
txtID.Text = .Cells(idx + 2, 0).Value
txtName.Text = .Cells(idx + 2, 1).Value
txtSurname.Text = .Cells(idx + 2, 2).Value
txtOccupation.Text = .Cells(idx + 2, 3).Value
cboMedical.Text = .Cells(idx + 2, 4).Value
txtOtherMedical.Text = .Cells(idx + 2, 5).Value
cboOREP.Text = .Cells(idx + 2, 6).Value
cboJob.Text = .Cells(idx + 2, 7).Value
cboFull.Text = .Cells(idx + 2, 8).Value
cboShort.Text = .Cells(idx + 2, 9).Value
txtRestrictions.Text = .Cells(idx + 2, 10).Value
txtComments.Text = .Cells(idx + 2, 11).Value
txtEmployee.Text = .Cells(idx + 2, 12).Value
txtExpiry.Text = .Cells(idx + 2, 13).Value
'Tests Performed
cbxPhysical.Value = .Cells(idx + 2, 14).Value
cbxUrine.Value = .Cells(idx + 2, 15).Value
cbxBP.Value = .Cells(idx + 2, 16).Value
cbxHeight.Value = .Cells(idx + 2, 17).Value
cbxWeight.Value = .Cells(idx + 2, 18).Value
cbxSpiro.Value = .Cells(idx + 2, 19).Value
cbxAudio.Value = .Cells(idx + 2, 20).Value
cbxCXR.Value = .Cells(idx + 2, 21).Value
cbxEndurance.Value = .Cells(idx + 2, 22).Value
cbxDrug.Value = .Cells(idx + 2, 23).Value
cbxAlcohol.Value = .Cells(idx + 2, 24).Value
cbxBlood.Value = .Cells(idx + 2, 25).Value
cbxOther.Value = .Cells(idx + 2, 26).Value
txtList.Text = .Cells(idx + 2, 27).Value
'Fitness Classification
optFit.Value = .Cells(idx + 2, 28).Value
optRestricted.Value = .Cells(idx + 2, 29).Value
optUnfit.Value = .Cells(idx + 2, 30).Value
optTemp.Value = .Cells(idx + 2, 31).Value
cboChronic.Text = .Cells(idx + 2, 32).Value
'Person Classification
txtVcat.Text = .Cells(idx + 2, 33).Value
txtHcat.Text = .Cells(idx + 2, 34).Value
txtLcat.Text = .Cells(idx + 2, 35).Value
txtPcat.Text = .Cells(idx + 2, 36).Value
txtBMcat.Text = .Cells(idx + 2, 37).Value
End With
End If
End Sub
Thank you all!
Bookmarks