Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jun 28, 2022
Solved

Query related to Cancel button with the help of VBA

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

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 28, 2022

    Excel 

    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
            DivisionSort
        Case 2
            CategorySort
        Case 3
            TotalSort
        Case Else
            tryAgain = MsgBox("Invalid Value! Try Again?", vbYesNo)
            If tryAgain = vbYes Then
                UserSortInput
            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

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 28, 2022

    Excel 

    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
            DivisionSort
        Case 2
            CategorySort
        Case 3
            TotalSort
        Case Else
            tryAgain = MsgBox("Invalid Value! Try Again?", vbYesNo)
            If tryAgain = vbYes Then
                UserSortInput
            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

Resources