Link sort variable to a message/input box

[gabe]

Hello, I'm not terribly experienced with VBA and was hoping that someone could point me in the right direction regarding something I'm trying to do. What I want to do is sort a list of job numbers in ascending order unless I specify the specific job codes that I want to come first in my list. I’ve got the code below which basically works but I'm not sure how to link the inputs highlighted in red to a message box in order to allow me to vary the input each time I run the code. It would be great if someone has a clear example as to how I can do this. I know this should be easy to do. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Also, would it be possible to loop through the sort input for multiple groupings of job codes? <o></o>

Sub SortAndInsertRowsTest2()
Dim Rng As Range, Dn As Range
Dim Lrow As Long, i As Long
Set Rng = Range(Range("A2"), Cells(2, Columns.Count).End(xlToLeft))
'
' Sort by custom list then job number
'
LR = Cells(Rows.Count, 4).End(xlUp).Row
Application.AddCustomList ListArray:=Array("2890", "2620", "2000")
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:= _
Range("C3:C" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder _
:="2890,2620,2000", DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:X" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[John_w]

Something like this for your first question:

Sub xyzzy()

    Dim sInput As String
    Dim jobCodesArray As Variant
    
    sInput = InputBox("Enter job codes separated by commas")
    
    If sInput <> "" Then
        jobCodesArray = Array(Split(sInput, ","))
        Application.AddCustomList ListArray:=jobCodesArray
    End If
        
End Sub

 

[gabe]

John, I've been on vacation and just got your reply. I tried to incorporate your code below but I don't think I've got it quite right. Can you take a look and let me know what I've done wrong?

Sub SortAndInsertRowsTest3()

Dim Rng As Range, Dn As Range
Dim Lrow As Long, i As Long
Dim sInput As String
Dim jobCodesArray As Variant
Set Rng = Range(Range("A2"), Cells(2, Columns.Count).End(xlToLeft))
'
' Sort by jobnbr & custom list
'
sInput = InputBox("Enter job codes separated by commas")
If sInput <> "" Then
jobCodesArray = Array(Split(sInput, ","))
Application.AddCustomList ListArray:=jobCodesArray
End If

LR = Cells(Rows.Count, 4).End(xlUp).Row
Application.AddCustomList ListArray:=Array("jobCodesArray")
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:= _
Range("C3:C" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder _
:="jobCodesArray", DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:X" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>