VBA: Choose which sort method through Input box?

[DougStroud]

I am doing a sort on a sheet. I would like to choose which sort method to use depending on what stage I am in. I was thinking I could use an input box and then have a list of the two choices show up, then select one or the other. Is this possible?


The first sort is sorting by Item# and the second sort is by Item Description. So If I could choose between "Item#" or "Description".

If this is possible, what is necessary and how?

Here is my code so far-

Option Explicit

Sub Sort()
Dim ws3 As Worksheet
Dim LRow As Long
Set ws3 = Workbooks("TGSImporter.xls").Sheets("Update")

'Variables
LRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row



ws3.Range("A1:I" & LRow).Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlGuess

ws3.Range("A1:I" & LRow).Sort Key1:=Range("C2"), Order1:=xlAscending, _
Header:=xlGuess


End Sub

Ps. I don't want them to run together, but rather independently...

thanks....

doug

 

[viquarshaikh]

Sub Sort() 
Dim ws3 As Worksheet 
Dim LRow As Long , strSortColumn as String
Set ws3 = Workbooks("TGSImporter.xls").Sheets("Update") 

'Variables 
LRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row 
strSortColumn = ucase(InputBox("Enter Column Name to sort " & vbcrlf & "Enter only Column Label B or C"))

if strSortColumn <> "C" and strSortColumn <>"B" then
       msgbox "Please select COlumn B or C"
       Exit Sub
End If

ws3.Range("A1:I" & LRow).Sort Key1:=Range(strSortColumn & "2"), Order1:=xlAscending, _ 
Header:=xlGuess 


End Sub

 

[DougStroud]

Viquarshaikh,
Worked beautifully, thanks.

What is the "vbcrlf"?

Many thanks

 

[viquarshaikh]

it's just to induce a line break...not necessary ... included only for neatness

 

[DougStroud]

V--
In the code you provided-- What is the "2" role, following the strSortColumn?

ws3.Range("A1:I" & LRow).Sort Key1:=Range(strSortColumn & "2"), Order1:=xlAscending, _ 
Header:=xlGuess 

End Sub

 

[DougStroud]

Follow-up question:

I have been working, fiddling w/ V's original solution. I have altered it and now have two issues, due to the changes. I have changed the Inputbox criteria and this has eliminated the association w/ the variable "strSortColumn". I cannot figure out how to incorporate it back into the code.

V's original code is commented out directly below the Inputbox line of code.

1) Requiring the user to enter a specific column, W or Y, the input box does not recoginize a value has been entered, (variable statment is gone). It returns the msgbox notifying me of this.
2) The sort functionality has ceased, crucial, the whole reason for the procedure. Which is directly related to the variable.

I am also looking for some info for this line of code: What is the "21" for, or better stated how does it factor into the sorting process, originally it was "2"? This may need to be adjusted... but I cannot find out what its role is. Part b. The "Key1", I cannot find any info on this...

ws1.Range("A5:AH" & LRow).Sort Key1:=Range(strSortColumn & "21"), Order1:=xlAscending, Header:=xlGuess

Entire Code:

Sub Sort()
Dim ws1 As Worksheet
Dim LRow As Long
Dim strSortColumn As String

Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")

LRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

       
   InputBox Title:="Sort Item Records", Prompt:=("Enter Column Letter: (W) or (Y)" & vbCrLf & _
    "Item# = (W)" & vbCrLf & "Item Description = (Y)"), Default:="Enter W or Y"
    
'strSortColumn = UCase(InputBox("Enter Column Name to Sort " & vbCrLf & "Enter only Column Label ""W"" or ""Y""" _
'& vbCrLf & "W= Item#" & vbCrLf & "Y= Item Description"))
    
            If strSortColumn <> "W" And strSortColumn <> "Y" Then
                MsgBox "Did You Not Want to Sort?", vbQuestion, "Not Sorted"
                Exit Sub
            End If

ws1.Range("A5:AH" & LRow).Sort Key1:=Range(strSortColumn & "21"), Order1:=xlAscending, Header:=xlGuess


[W5].Activate
End Sub

Thanks,

Doug

 

[DougStroud]

*Bump*
Could I ask one of the experienced VBA's here to have a look at this. I have been working w/ this code since yesterday... albeit most likely a simple thing, is giving me fits...

 

[DougStroud]

Solved**

 

[viquarshaikh]

Is this solved or is there any problem still with this one?

 

[DougStroud]

Hi V-
Got it resolved just today...

Thanks for checking back in

Doug