Advice to add edit to existing code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,960
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,960
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
3,960
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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