Advice to add edit to existing code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I am using the code supplied below.
Currently i make a selection from the drop down list & press CommandButton1 to run the code.
The selected column is then sorted perfect A-Z & then as shown in red below cell A4 is selected.

Im looking to have the ability to not select A4 all the time BUT to select the cell in row 4 for which column i asked to be sorted.
So if i select YEAR from the drop down list i expect after the sort cell D to be selected.

Im not sure on how to do thi but i assuming an IF statement would be the answer,Something like,
Rich (BB code):
If
 ComboBox1 selection = A Then
 Cell A4.select
Else If
ComboBox1   selection = B Then
Cell B4.Select

As you can see im poor at this but can follow advice once given.
Thanks

Rich (BB 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 "VIN NUMBER"
SortColumn = "A"

Case "VEHICLE"
SortColumn = "B"

Case "CUSTOMER"
SortColumn = "C"

Case "YEAR"
SortColumn = "D"

Case "HONDA NUMBER"
SortColumn = "E"

Case "SUPPLIED"
SortColumn = "F"

Case "DATE"
SortColumn = "G"

End Select
Range("A4").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
Private Sub UserForm_Initialize()
Dim rheadings As Range
Dim cl As Range
Set rheadings = Worksheets("HONDA LIST").Range("A2:G2")
For Each cl In rheadings
Me.ComboBox1.AddItem cl.Value
Next cl
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
This is what ive got so far.
It selects the row 4 cell fine but doesnt sort.
Removing the ElseIf part then it sorts.

What did i do wrong please.


VBA 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 "VIN NUMBER"
SortColumn = "A"

Case "VEHICLE"
SortColumn = "B"

Case "CUSTOMER"
SortColumn = "C"

Case "YEAR"
SortColumn = "D"

Case "HONDA NUMBER"
SortColumn = "E"

Case "SUPPLIED"
SortColumn = "F"

Case "DATE"
SortColumn = "G"

End Select

If ComboBox1 = "VIN NUMBER" Then
Range("A4").Select

ElseIf ComboBox1 = "VEHICLE" Then
Range("B4").Select

ElseIf ComboBox1 = "CUSTOMER" Then
Range("C4").Select

ElseIf ComboBox1 = "YEAR" Then
Range("D4").Select

ElseIf ComboBox1 = "HONDA NUMBER" Then
Range("E4").Select

ElseIf ComboBox1 = "SUPPLIED" Then
Range("F4").Select

ElseIf ComboBox1 = "DATE" Then
Range("G4").Select

ElseIf 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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
I believe this is correct

Rich (BB 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 "VIN NUMBER"
SortColumn = "A"

Case "VEHICLE"
SortColumn = "B"

Case "CUSTOMER"
SortColumn = "C"

Case "YEAR"
SortColumn = "D"

Case "HONDA NUMBER"
SortColumn = "E"

Case "SUPPLIED"
SortColumn = "F"

Case "DATE"
SortColumn = "G"

End Select

If ComboBox1 = "VIN NUMBER" Then
Range("A4").Select

ElseIf ComboBox1 = "VEHICLE" Then
Range("B4").Select

ElseIf ComboBox1 = "CUSTOMER" Then
Range("C4").Select

ElseIf ComboBox1 = "YEAR" Then
Range("D4").Select

ElseIf ComboBox1 = "HONDA NUMBER" Then
Range("E4").Select

ElseIf ComboBox1 = "SUPPLIED" Then
Range("F4").Select

ElseIf ComboBox1 = "DATE" Then
Range("G4").Select
End If

If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
End If

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
    Unload Me
    End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,836
Messages
5,833,910
Members
430,243
Latest member
madcock83

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
Top