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

ipbr21054

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

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You don't need the command button, just the combobox. Did you add a combobox?
 
Upvote 0
Yes to ComboBox

I have a command button which opens the userform
 
Upvote 0
Post the code for the command button.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,714
Members
448,294
Latest member
jmjmjmjmjmjm

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