Code opens wrongly selected customer from listbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,683
Office Version
  1. 2007
Platform
  1. Windows
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


Rich (BB 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).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
 
Hi,
I understand what you are saying but take a look at my screenshots
This is using the code

Rich (BB 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
 

Attachments

  • EaseUS_2023_09_19_16_23_02.jpg
    EaseUS_2023_09_19_16_23_02.jpg
    88.9 KB · Views: 10
  • EaseUS_2023_09_19_16_23_20.jpg
    EaseUS_2023_09_19_16_23_20.jpg
    126.1 KB · Views: 16
  • EaseUS_2023_09_19_16_23_38.jpg
    EaseUS_2023_09_19_16_23_38.jpg
    124.6 KB · Views: 13
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If i use the code above BUT change this line to
Rich (BB code):
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
 
Upvote 0
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 ?
 
Upvote 0
You don't need selection.row as you already know the row number:

Code:
Database.LoadData Sheets("DATABASE"), rw

If the right row is selected, then there must be an issue somewhere in the LoadData code.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top