Sort using an input box to select the column

[lmmay]

I am using the following to sort an excel sheet:

Sub Sort()
'Unprotect Worksheet
ActiveSheet.UnProtect Password:=" cover700"
Selection.Sort Key1:=Range(Selection.Address), Order1:=xlAscending, DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Password:=" cover700", Scenarios:=True _
, AllowFormattingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub

Except for the last line, the code works well sorting on column 1.
I need to modify the script to allow the user to select a different column to sort using an input box.

Any help would be greatly appreciated.

 

[DanteAmor]

Try this

Sub Sort()
'Unprotect Worksheet
    Dim waddress As Variant
    ActiveSheet.Unprotect Password:=" cover700"
    waddress = Application.InputBox("Select column", "SORT", Selection.Address, , , , , 8)
    If waddres = "" Then Exit Sub
    Selection.Sort Key1:=Range(waddress & 1), Order1:=xlAscending, DataOption1:=xlSortNormal
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Password:=" cover700", Scenarios:=True _
    , AllowFormattingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub

 

[lmmay]

The routine exits at If waddress=""

 

[dmt32]

The routine exits at If waddress=""

from VBA Helfile

Type is 8, InputBox returns a Range object. You must use the Set statement to assign the result to a Range object

Try updating your code using Set Statement.

Dave

 

[DanteAmor]

The routine exits at If waddress=""

Sorry my mistake.
Try this and tell me.

Sub Sort()
'Unprotect Worksheet
    Dim waddress As Variant
    ActiveSheet.Unprotect Password:=" cover700"
    waddress = Application.InputBox("Select column", "SORT", Selection.Address, , , , , 8)
    If waddress = False Then Exit Sub
    Selection.Sort Key1:=Range(waddress & 1), Order1:=xlAscending, DataOption1:=xlSortNormal
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Password:=" cover700", Scenarios:=True _
    , AllowFormattingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub

 

[lmmay]

what is "waddress"?

 

[DanteAmor]

what is "waddress"?

Dim waddress As Variant

waddress is a variable, in this variable the column that you select in the inputbox is stored.