ListBox selection selects wrong customer on worksheet

[ipbr21054]

Hi,
I am using the code as shown.

I populate the Listbox & i then make a selection.
I should then be on the worksheet looking at the customer that i selected from the Listbox but this isnt the case.

Example of whats happening.
The listbox shows 4 customers of which are ABCD
I select A but i am taken C
I then run the code again to populate the Listbox.
I then select A again but taken to D

As you would expect whatever you select you should then be taken to that customer.
Also when the customer is selected on the worksheet it selects the cell in column I but i wish to have it select column A
Do you see what is causing this please

Private Sub ListBox1_Click()
With ThisWorkbook.Worksheets("DATABASE")
Dim data As Variant

    data = .Range("I6:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
  
   Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        
   Unload ProgrammerForm
   
   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 ProgrammerForm
 End If
 
End Sub

 

[jolivanes]

How is the Listbox populated?
In your "Cells.Find" line, which range do you want to search?

 

[ipbr21054]

Populate using this code below.

The code searches for a value in column I6 & down the page.
When i select a customet in the Listbox i expect to be taken to that customer & have the cell in column A selected

Private Sub SearchForm_Click()
  Dim R As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("DATABASE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "150;210;190;190;50"
    If ComboBox1.Value = "" Then Exit Sub
    Set R = Range("I6", Range("I" & Rows.Count).End(xlUp))
    Set f = R.Find(ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Offset(, -8).Value
              .List(i, 2) = f.Offset(, -5).Value
              .List(i, 3) = f.Offset(, -3).Value
              .List(i, 4) = f.Offset(, -2).Value
              .List(i, 5) = f.Offset(, -1).Value
              
              added = True
              Exit For
          End Select
        Next
        If added = False Then
              .AddItem f.Value
              .List(.ListCount - 1, 1) = f.Offset(, -8).Value
              .List(.ListCount - 1, 2) = f.Offset(, -5).Value
              .List(.ListCount - 1, 3) = f.Offset(, -3).Value
              .List(.ListCount - 1, 4) = f.Offset(, -2).Value
              .List(.ListCount - 1, 5) = f.Offset(, -1).Value
              
        End If
        Set f = R.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      .TopIndex = 0
    Else
      
    End If
  End With
  
End Sub

 

[jolivanes]

Just to do a quick test, I used this:

Private Sub UserForm_Initialize()
    ListBox1.List = Sheets("Sheet1").Range("I6:I" & Sheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row).Value
End Sub

Private Sub ListBox1_Click()
    Sheets("Sheet1").Range("I6:I" & Sheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row).Find(ListBox1, , , 1).Offset(, -8).Select
End Sub

It selected in Column A in the same Row as the value selected in the ListBox in Column I

 

[ipbr21054]

Hi,
That made no diference for me.
Let me advise.
I select from a combobox drop down on my userform a value.
This value is always in column I
Once the value is found it populates the Listbox with value taken from worksheet in columns A D F G

Using this code supplied here is an example of what happens.
I select the first value in the combobox drop down & the listbox is populated.
I select a value in the Listbox & ok the incorrect customer is selected BUT the process workewd.
BUT
If i then select say the seconds value in the Combobox drop down & do the same again then when i select a listbox value i get RTE
Something to do with the -1 in the code sorts it but obviously cant keep chaing it.

My thought of how it should work is as follows.
Make a combobox selection, values are then added to listbox.
In the lisbox are the values returned from my worksheet.
All that needs to happen is when making a listbox selection just take me to the customer in question.

See screen shot example.
Combobox search value is in column I
Values taken from worksheet to listbox come from columns A D F G
So regardless of anything the code just needs to look at my listbox selection & then the value it was loaded with from column A just take me there.

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, -1)
     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 ProgrammerForm
    End If
   
  End Sub

 

[dmt32]

Hi,

When you filter data from your database to the listbox there is no correlation between the two i.e the rows listindex in your listbox will not match the rows in your database.

The normal method to overcome this is to copy the database row number to a hidden column in the listbox & reference this to get the correct record.



I have only lightly tested but see if these updated codes will do what you want

Make a BACKUP of your workbook & replace your current codes with following

Dim wsDatabase      As Worksheet
Private Sub ListBox1_Click()
    Dim rw          As Long
    Dim answer      As VbMsgBoxResult
    
    With Me.ListBox1
        'get database row number
        rw = Val(.Column(.ColumnCount - 1, .ListIndex))
        If rw = 0 Then Exit Sub
    End With
    
    With wsDatabase
        .Activate
        .Range("A" & rw).Select
    End With
    
    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 ProgrammerForm
    End If
    
End Sub

Private Sub SearchForm_Click()
    Dim arr          As Variant
    Dim r             As Long, c As Long
    Dim Search        As String
    
     'search column
    Const SearchColumn As Long = 9
    
    Search = UCase(Me.ComboBox1.Value)
    
    'size data array
    arr = wsDatabase.Range("A1").CurrentRegion.Value
    
    With Me.ListBox1
    
        .RowSource = ""
        .Clear
        If Len(Search) > 0 Then
            For r = 2 To UBound(arr, xlRows)
                If UCase(arr(r, SearchColumn)) = Search Then
                    .AddItem arr(r, SearchColumn)
                    
                    For c = 1 To .ColumnCount - 2
                                                                   'A 'D 'F 'G
                        .List(.ListCount - 1, c) = arr(r, Choose(c, 1, 4, 6, 7))
                    Next c
                    
                    'row number (hidden column)
                    .List(.ListCount - 1, c) = r
                End If
            Next r
        End If
        
    End With
    
End Sub

Private Sub UserForm_Initialize()

    Set wsDatabase = ThisWorkbook.Worksheets("Database")
    
    With Me.ListBox1
        .ColumnHeads = False
        'size listbox
        .ColumnCount = 6
        .ColumnWidths = "150;210;190;190;50,0"
    End With
    
End Sub

Note the variable at the top of the codes – this variable MUST be placed at very TOP of your userforms code page OUTSIDE any procedure.



Hope Helpful



Dave

 

[ipbr21054]

Sometimes when clickin a value in the listbox nothing happens at all.
Othertimes i get a RTE 91
When i debug this line is in yellow.

        .Activate

 

[dmt32]

Difficult to say as I only lightly tested with a dummy file & seemed to work ok.

Can you place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave

 

[ipbr21054]

Hi,
See if this download works for you.

TEST FILE DOWNLOAD

 

[dmt32]

Hi,
classic case of OP not following directions given, making amendments to published code as well as code usage being different to published code (you have code in combobox change event not a button click event)

DELETE all the code in your userform & replace with following & please, do not make any changes

Dim wsDatabase As Worksheet ' << You ignored my note to ensure this variable was placed here
Private Sub CloseForm_Click()
Unload ProgrammerForm
End Sub

Private Sub ComboBox1_Change()
 Dim arr          As Variant
    Dim r             As Long, c As Long
    Dim Search        As String
    
     'search column
    Const SearchColumn As Long = 9
    
    Search = UCase(Me.ComboBox1.Value)
    
    'size data array
    arr = wsDatabase.Range("A1").CurrentRegion.Value
    
    With Me.ListBox1
    
        .RowSource = ""
        .Clear
        If Len(Search) > 0 Then
            For r = 2 To UBound(arr, xlRows)
                If UCase(arr(r, SearchColumn)) = Search Then
                    .AddItem arr(r, SearchColumn)
                    
                    For c = 1 To .ColumnCount - 2
                                                                   'A 'D 'F 'G
                        .List(.ListCount - 1, c) = arr(r, Choose(c, 1, 4, 6, 7))
                    Next c
                    
                    'row number (hidden column)
                    .List(.ListCount - 1, c) = r
                End If
            Next r
        End If
        
    End With
    
End Sub

Private Sub ListBox1_Click()
'Dim wsDatabase      As Worksheet '<< you add edthis line - why???
    Dim rw          As Long
    Dim answer      As VbMsgBoxResult
    
    With Me.ListBox1
        'get database row number
        rw = Val(.Column(.ColumnCount - 1, .ListIndex))
        If rw = 0 Then Exit Sub
    End With
    
    With wsDatabase
        .Activate
        .Range("A" & rw).Select
    End With
    
    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 ProgrammerForm
    End If
    
End Sub

Private Sub UserForm_Initialize()
    With ComboBox1
      .AddItem "AUTEL IM 508"
      .AddItem "HANDY BABY"
      .AddItem "KDX 2"
      .AddItem "NANOCOM"
      .AddItem "SKP-900"
      .AddItem "SKP-900 IMMO 1"
      .AddItem "SKP-900 IMMO 2"
      .AddItem "SKP-900 IMMO 3"
      .AddItem "SKP-900 FOCUS"
      .AddItem "SKP-KEYLESS 1"
      .AddItem "SKP-KEYLESS 2"
      .AddItem "SKP-OLD 3 PIN PLUG"
      .AddItem "T300 TYPE 2A"
      .AddItem "T300 TYPE 2B"
      .AddItem "T300"
      .AddItem "TRS 5000"
      .AddItem "VVDI KEY TOOL"
    End With
        Me.StartUpPosition = 0
    Me.Top = Application.Top + 50  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 90 ' LEFT / RIGHT OF SCREEN
    
    Set wsDatabase = ThisWorkbook.Worksheets("Database")
    
    With Me.ListBox1
        .ColumnHeads = False
        'size listbox
        .ColumnCount = 6
        .ColumnWidths = "150;210;190;190;50,0"
    End With
End Sub

Dave