Query related to Cancel button with the help of VBA

Super Contributor

Hello Everyone, 

I have written sort code with the help of VBA code, it run successfully. 

here we added exception handling for "OK" and "Cancel" buttons in order for our sorting to continue.

But what if the user decides to not continue with sorting and abort the process, how do I enable the user to click cancel button without throwing any error or taking it back to sorting again ? 


Please help.


Here is a file attached

2 Replies
best response confirmed by Zain123 (Super Contributor)


New version:

Public Sub UserSortInput()
    Dim sortorder As Integer
    Dim promptMSG As String
    Dim tryAgain As Integer

    promptMSG = "How would you like to sort the list?" & vbCrLf & _
        "1 - Sort by Division" & vbCrLf & _
        "2 - Sort by Category" & vbCrLf & _
        "3 - Sort by Total"

    sortorder = Val(InputBox(Prompt:=promptMSG, Title:="Sort Order"))
    Select Case sortorder
        Case 0
            Exit Sub
        Case 1
        Case 2
        Case 3
        Case Else
            tryAgain = MsgBox("Invalid Value! Try Again?", vbYesNo)
            If tryAgain = vbYes Then
            End If
    End Select
End Sub

Public Sub DivisionSort()
    ' sorts the list by the Division
    Columns("A:F").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes
End Sub

Public Sub CategorySort()
    ' sorts the list by the Category
    Columns("A:F").Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlYes
End Sub

Public Sub TotalSort()
    ' sorts the list by the Total
    Columns("A:F").Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes
End Sub
Thank you so much sir.