Advice for userform to sort then go to.....

[ipbr21054]

Hi,
On my worksheet in column B i have a list of vehicles.
These vechiles at present are unsorted because column D is in the A-Z sort option.

Imagine the vehicles are.
ACCORD
CIVIC
INSIGHT
JAZZ
ETC ETC & there are many instances of the same car.
So when column B is sorted A-Z so ACCORD has 55 rows followed by CIVIC with 104 rows etce etc.

I need to make a userform which each model of vehicle in the list so when i click say INSIGHT i would like to be taken to the first instance of INSIGHT
Currently im scrolling through lots of rows before i even get to the I section.

 

[mumps]

Post the code for the command button. Is the combobox named "ComboBox1"?

 

[ipbr21054]

My code in post #2 works fine.

If i can then go to first instance of combobox1 selection after the sort is done thats all

 

[ipbr21054]

Yes it is but see my last post

 

[mumps]

Click here to download your file.

 

[ipbr21054]

Yep that works but my code still fails.

CommandButton1 code is

Private Sub CommandButton1_Click()
HondaListSortAndGoToVehicle.Show
End Sub

So the userform HondaListSortAndGoToVehicle code is,

Private Sub ComboBox1_Change()
    Dim fnd As Range
    Set fnd = Sheets("HONDA LIST").Range("B:B").Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
    fnd.Select
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, WS As Worksheet
    Set WS = ThisWorkbook.Sheets("HONDA LIST")
    WS.Cells(1, 2).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In WS.Range("B4", WS.Range("B" & WS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
            ComboBox1.AddItem Rng
        End If
    Next
End Sub

Userform screenshot shown

 

[mumps]

Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com? Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[ipbr21054]

Just doing that now.

I can tell you that when i click the command button now i see the userform,i make a selection but i am not taken to it.



I say this as a slimmed done version does something the full version doesnt if that make sense

 

[mumps]

Can you de-sensitize the data?

 

[ipbr21054]

I have many other sheets on the same workbook,last time made anerror & took ages to find it

 

[mumps]

Just include the "HONDA LIST" sheet (de-sensitized) and delete the others.