Code isnt working correctly & selects the incorrect cell on my worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I am using the code shown below & wondering if you see a reason for which it isnt working correctly.

I have a userform where in a textbox i would type say JAZZ
In the list box all the records found on the worksheet for JAZZ are now shown.
The list would show customers name & say registration to now make it easier on the eye.

I look through the list & find the record i need to view.
So in this example lets say the populated list is as below.

JOM JONES
ROBER PLANT
PAUL WELLER
ELVIS
STING

So i select TOM JONES in the list, the userform closes BUT STING is selected on the worksheet NOT TOM JONES
I do the same again BUT ELVIS is then selected.
I do the same again BUT PAUL WELLER is then selected.

So it looks like no matter what i select the code selects the last name in the list then works its way up the list.

Obviously if i select JOM JONES i need TOM JONES to be selected.

Many Thanks.

Rich (BB code):
Private Sub TextBoxVehicle_Change()
TextBoxVehicle = UCase(TextBoxVehicle)
  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 = 3
    .ColumnWidths = "210;260;80;"
    If TextBoxVehicle.Value = "" Then Exit Sub
    Set R = Range("D6", Range("D" & Rows.Count).End(xlUp))
    Set f = R.Find(TextBoxVehicle.Value, LookIn:=xlValues, LookAt:=xlPart)
    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(, -3).Value
              .List(i, 2) = f.Offset(, -2).Value
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -3).Value
          .List(.ListCount - 1, 2) = f.Offset(, -2).Value
        End If
        Set f = R.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
      Else
      MsgBox "NO ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET ITEM SEARCH"
      TextBoxVehicle.Value = ""
      TextBoxVehicle.SetFocus
    End If
  End With
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You are showing the code that populates the listbox, but that is probably not the source of the problem. Please show us the code that selects the cell on the worksheet that was selected in the list.

BTW you did not ask but I think your filter is broken. That list should be generated when you type "POP". :) ;)
 
Upvote 0
Would this be it ?

Rich (BB code):
Private Sub ListBox1_Click()
 Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
  Unload DatabaseSearchForm
End Sub
 
Upvote 0
I can't see what's wrong. Regardless of how you load the data, once the data is there the user clicks, and that is the value. I am assuming this is a SingleSelect listbox (the user can only select one item at a time).

I was expecting your Find code to reference the wrong list item but by referencing .Value you guarantee that it's the right one.

If you have some way to share your file I would be happy to troubleshoot it.

By the way, you are searching in Cells, which will be all cells of whatever worksheet is currently active. I suggest you explicitly refer to
VBA Code:
Sheets("DATABASE").Range("D:D").Find(
But I don't think that will necessarily solve your poblem.
 
Upvote 0
Here is the file.

Select Search Worksheet
In VEHICLE box type JAZZ
Select JAZZ CUSTOMER 1
Then see what is selected on worksheet.

Do this same process a few times & you will see that Cell D6 isnt selected each time

Test File
 
Upvote 0
Your code to search is not looking for Customer 1, it is just looking for "JAZZ" anywhere on the entire sheet. So it always finds the next one after the currently selected cell. So it's working perfectly.

What do you want it to do instead? Do you want to search for "Customer 1" in column A? I tested this in your file:

VBA Code:
Private Sub ListBox1_Click()

  With ListBox1

      Cells.Find(What:=.List(.ListIndex, 1), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
             False).Activate

  End With
  
  Unload DatabaseSearchForm

End Sub
 
Upvote 0
Ok
I thought it would then go to that person as the name and vehicle was on the same line in the list.

Example.
I’ve forgot the name of the customer but I know he drives a Jazz.
So I enter jazz to pull up all the customers.
I then see the name of which it then jogged my memory so I then click on it thinking it would take me to that customers record.
 
Upvote 0
You click on it then search on Listbox1.Value. The Value of a listbox is the current selection in the first column. So it is searching for "JAZZ". I provided modified code that would look specifically in column A, and look for the value of the selected row in the second column.

Hopefully you will try it and it will work for you in your original file. It worked in my test on your sample file.
 
Upvote 0
Morning,
Ive applied that to my sheet but i dont understand what part of what code selects which cell on the worksheet.
This is what happens.
I enter some partial letters in NAME then select from the listbox.
On the worksheet the cell in column B is selected.

I do the same for the other textboxes with the following results.
REGISTRATION cell in column A
VEHICLE cell in column A
KEY CODE cell in column B
CHASSIS NUMBER cell in column B

So why different columns each time ?

Ive look at the code for REGISTRATION which selects A & also KEY CODE which selects B
But i dont see anything different that makes the code select column A or B
 
Upvote 0
The code you wrote for Listbox1_Click, which is what I revised in post #6 above, is what selects the cell on the worksheet.

Your question only addressed one textbox so I didn't know you had issues with the others. I'll have to look at your file again as context for your latest post.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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