Code opens wrongly selected customer from listbox

[ipbr21054]

Hi,
Code in use is shown below.
In a userform i search for a customer.
My listbox is populated with the results.

I can select any customer in the listbox & that customers info is then shown on the screen no problem.
Having said that i have an issue where cell A5 is TOM JONES & TOM PETTY
If my search is for TOM i select TOM JONES from the listbox BUT TOM PETTY is shown on the screen.

I added 2 new rows so ANDY 1 is row A6 & ANDY 2 is row A5
I search for ANDY & select ANDY 1 inT ANDY 2 is loaded.

Do you see anything in the code that has an issue with the cell A5 value loading wrongly

Private Sub ListBox1_Click()
     Dim rw As Long
     Dim ws As Worksheet
     Dim answer As Integer
     Set ws = ThisWorkbook.Sheets("Database")
    If ListBox1.ListIndex = -1 Then Exit Sub
     rw = ListBox1.List(ListBox1.ListIndex, 3)
     ws.Range("A" & rw).Select
     Unload Me
     
     
   answer = MsgBox("OPEN CUSTOMERS FILE IN MAIN DATABASE ?", vbYesNo + vbInformation, "OPEN DATABASE MESSAGE")
   If answer = vbYes Then
   
   Database.LoadData Sheets("DATABASE"), Selection.Row

    Else
    Unload DatabaseSearchForm
    End If
  End Sub

 

[ipbr21054]

Hi,
I understand what you are saying but take a look at my screenshots
This is using the code

Private Sub ListBox1_Click()
     Dim rw As Long
     Dim ws As Worksheet
     Dim answer As Integer
     Set ws = ThisWorkbook.Sheets("Database")
    If ListBox1.ListIndex = -1 Then Exit Sub
     rw = ListBox1.List(ListBox1.ListIndex, 3)
     ws.Range("A" & rw + 1).Select
     Unload Me
     
   answer = MsgBox("OPEN CUSTOMERS FILE IN MAIN DATABASE ?", vbYesNo + vbInformation, "OPEN DATABASE MESSAGE")
   If answer = vbYes Then
   
   Database.LoadData Sheets("DATABASE"), Selection.Row

    Else
    Unload DatabaseSearchForm
    End If
  End Sub

 

[ipbr21054]

If i use the code above BUT change this line to

ws.Range("A" & rw ).Select

I select a customer in the listbox & it selects the correct customer on the worksheet & opens the correct file.

The only failure is,
I select the first customer in the listbox, its correctly selected on the worksheet BUT when it opens the file for the next customer down thus being A H TURNER & SON

 

[ipbr21054]

Oops, misread the code. The row number is stored in the listbox in column 4. It appears the wrong row is stored for that item but as that's not done by this code, it's hard to comment.

How can i show you the code you need to see ?

 

[RoryA]

You don't need selection.row as you already know the row number:

Database.LoadData Sheets("DATABASE"), rw

If the right row is selected, then there must be an issue somewhere in the LoadData code.

 

[davesexcel]

Your code is selecting 5 rows more than the list index, if it was only selecting 4 more than the correct rows would be selected. You don't show what is in the other columns of the list box.

 

[ipbr21054]

Ive now used the code as Rory advised in post #14

I think i will just puit up with this as its wasting to much of our time>

Many thanks for the help though today