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

ipbr21054

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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
Hi,
Below is what i have just put in place.

The userform is populated in the row source field as Table27

I think the below will work in respect of selecting a vehicle to sort BUT i need some advice please so once sorted it then goes to the first instance of what was selected in the drop down list
Also can this be written so its less code,something like.

Private Sub UserForm_Initialize()
Worksheets"(INFO")
Table27
End Sub


Code:
Private Sub CommandButton1_Click()
Dim x As Long
Dim ws As Worksheet
Set ws = Sheets("HONDA LIST")
Dim SortColumn As String

Select Case ComboBox1
Case "VEHICLE"
SortColumn = "B"

Case "ACCORD"
SortColumn = "B"

Case "CIVIC FACELIFT 13-ON"
SortColumn = "B"

Case "CIVIC KEYLESS"
SortColumn = "B"

Case "CIVIC OLD 99-06"
SortColumn = "B"

Case "CIVIC PSI 06-13"
SortColumn = "B"

Case "CRV JHLRD"
SortColumn = "B"

Case "CRV SHSRD"
SortColumn = "B"

Case "CRV SHSRE"
SortColumn = "B"

Case "FRV"
SortColumn = "B"

Case "HRV"
SortColumn = "B"

Case "INSIGHT"
SortColumn = "B"

Case "JAZZ GD"
SortColumn = "B"

Case "JAZZ GE"
SortColumn = "B"

Case "JAZZ GG"
SortColumn = "B"

Case "LEGEND"
SortColumn = "B"

Case "S2000"
SortColumn = "B"

Case "STEPWAGON"
SortColumn = "B"

Case "STREAM"
SortColumn = "B"

End Select
If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess
End With
    End If
    Unload Me
    End Sub
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
You don't have to sort column B to go to the first instance of the car. Insert a combobox on the userform. Place the code below in the userform code module:
VBA Code:
Private Sub ComboBox1_Change()
    Dim fnd As Range
    Set fnd = Sheets("Sheet1").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("Sheet1")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In WS.Range("B2", 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
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
OK,
Before i do that if column B is all unsorted,after the first instance is founds say JAZZ GD i then need to look down another row then another etc etc whilst these are also all JAZZ GD
Otherwise it will find the first instance of JAZZ GD but the next row could be ACCORD

This is why i had sorted it first
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
Try:
VBA Code:
Private Sub ComboBox1_Change()
    Dim fnd As Range
    Set fnd = Sheets("Sheet1").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("Sheet1")
    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("B2", 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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
Ok
Ive used that code & changed sheet name etc etc but after pressing Command Button 1 I see RTE70 Permission denied.
When i debug i see the following red below shown in yellow.

I dont see any userform appear at all

Rich (BB code):
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("B2", 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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
You don't need the command button, just the combobox. Did you add a combobox?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
Yes to ComboBox

I have a command button which opens the userform
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
The command button is on the worksheet HONDA LIST
I click on it & i would then expect to see the userform.

What happens when i click it is that RTE
 

Watch MrExcel Video

Forum statistics

Threads
1,102,110
Messages
5,484,812
Members
407,467
Latest member
spurs50

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top