Jun 28 2022 01:58 AM - edited Jun 28 2022 03:32 AM
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
Jun 28 2022 03:45 AM
SolutionNew 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
Jun 28 2022 05:28 AM
Jun 28 2022 03:45 AM
SolutionNew 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