Hi All,

I am new to macro's and am in desperate need of some help. I have a userform on which the user enters a fault code into a textbox eg "X01234". The user then clicks a button (commandbutton3), search, which then searchs through a spreadsheet to find the fault searched for. The fault code can have more than one occurance so after the search, a listbox is then populated with the fault code and the date afterwards eg. "X01234 : 22/04/07".

Say the listbox has 5 different entries, the same fault code but different dates, I then want the user to double click the listbox entry they want to view and this then automatically enters the data for that fault into textboxes below. I wanted them to be able to click on the various listbox fault entries and this then changes the data in the textboxes to show all the relevant information for that particular fault.

I can perform the search and populate the listbox but I can seem to link the listbox fault/date entries into the spreadsheet row. Can anyone help!
Here is what I have so far for the Search code and the DoubleClick Listbox:

Private Sub CommandButton3_Click()
'Procedure level variables
Dim lCount As Long
Dim lOccur As Long
'Clear any old entries
On Error Resume Next
ListBox1.Clear
On Error GoTo 0
'Set range variable to first cell in table.
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strFind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Columns(1), strFind1)
'Loop only as many times as strFind1 occurs
For lCount = 1 To lOccur
'Set the range variable to the found cell. This is then also _
used to start the next Find from (After:=rCell)
Set rCell = rRange.Columns(1).Find(What:=strFind1, After:=rCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'Used to not sh ow message box for no value found.
'Add the address of the found cell and the cell on the _
same row but 2 columns to the right.
ListBox1.AddItem rCell.Value & " : " & rCell(1, 2).Value

Next lCount
End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Application.Goto Range(rCell.Address), True
TextBox2.Value = ActiveCell
TextBox3.Value = ActiveCell.Offset(0, 1)
TextBox4.Value = ActiveCell.Offset(0, 2)
TextBox5.Value = ActiveCell.Offset(0, 3)

TextBox3.Value = Format(TextBox3.Value, "dd-mm-yyyy")
TextBox4.Value = Format(TextBox4.Value, "hh:mm")

End Sub

Hope to hear from you soon, many thanks, Adam

Why are you using a spreadsheet to source your data from? surely storing the information in Access would solve the issue straight away.

David