Forum Discussion

Excel's avatar
Excel
Iron Contributor
Oct 18, 2022

Question related to VBA

Hello Everyone, 

 

Got the two Msgbox I have placed for error in Sort order or Sort Sequence Since entering incorrect value in any situation gives the same number as 6 so only 1 Try again variable will work or both Try again and Try again sort order are required. While keeping both I am getting Invalid Value Pop up box twice. 

 

So, what should i write code if i click in cancel button then it should show the Pop up box ?

 

Please help..

 

Here is below attached file..

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Oct 18, 2022

    Excel 

    I'd do it like this. It lets the user exit by pressing Cancel in either of the input boxes

    Public Sub SortUserInput()
    Dim SortOrder As Integer
    Dim PromptMsg As String
    Dim SortSequence As Integer
    Dim SequencePromptMsg As String

    On Error GoTo ErrorHandler

    Do
        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(PromptMsg, "Sort Order"))
        Select Case SortOrder
            Case 0 ' Canceled
                Exit Sub
            Case 1, 2, 3 ' Correct input
                Exit Do
            Case Else ' Incorrect input
                MsgBox "Invalid input. Try again.", vbExclamation
        End Select
    Loop

    Do
        SequencePromptMsg = "Which Way You would like to Sort the List?" & vbCrLf & _
            "1 - Ascending" & vbCrLf & _
            "2 - Descending"
        SortSequence = Val(InputBox(SequencePromptMsg, "Sort Sequence"))
        Select Case SortSequence
            Case 0 ' Canceled
                Exit Sub
            Case 1, 2 ' Correct input
                Exit Do
            Case Else ' Incorrect input
                MsgBox "Invalid input. Try again.", vbExclamation
        End Select
    Loop

    If SortOrder = 1 And SortSequence = 1 Then
            Sort_Division_Asc
    ElseIf SortOrder = 1 And SortSequence = 2 Then
            Sort_Division_Desc
    ElseIf SortOrder = 2 And SortSequence = 1 Then
            Sort_Category_Asc
    ElseIf SortOrder = 2 And SortSequence = 2 Then
            Sort_Category_Desc
    ElseIf SortOrder = 3 And SortSequence = 1 Then
            Sort_Total_Asc
    ElseIf SortOrder = 3 And SortSequence = 2 Then
            Sort_Total_Desc
    End If

    Exit Sub

ErrorHandler:
    MsgBox "Something went wrong!", vbCritical
End Sub

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 18, 2022

    Excel 

    I'd do it like this. It lets the user exit by pressing Cancel in either of the input boxes

    Public Sub SortUserInput()
    Dim SortOrder As Integer
    Dim PromptMsg As String
    Dim SortSequence As Integer
    Dim SequencePromptMsg As String

    On Error GoTo ErrorHandler

    Do
        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(PromptMsg, "Sort Order"))
        Select Case SortOrder
            Case 0 ' Canceled
                Exit Sub
            Case 1, 2, 3 ' Correct input
                Exit Do
            Case Else ' Incorrect input
                MsgBox "Invalid input. Try again.", vbExclamation
        End Select
    Loop

    Do
        SequencePromptMsg = "Which Way You would like to Sort the List?" & vbCrLf & _
            "1 - Ascending" & vbCrLf & _
            "2 - Descending"
        SortSequence = Val(InputBox(SequencePromptMsg, "Sort Sequence"))
        Select Case SortSequence
            Case 0 ' Canceled
                Exit Sub
            Case 1, 2 ' Correct input
                Exit Do
            Case Else ' Incorrect input
                MsgBox "Invalid input. Try again.", vbExclamation
        End Select
    Loop

    If SortOrder = 1 And SortSequence = 1 Then
            Sort_Division_Asc
    ElseIf SortOrder = 1 And SortSequence = 2 Then
            Sort_Division_Desc
    ElseIf SortOrder = 2 And SortSequence = 1 Then
            Sort_Category_Asc
    ElseIf SortOrder = 2 And SortSequence = 2 Then
            Sort_Category_Desc
    ElseIf SortOrder = 3 And SortSequence = 1 Then
            Sort_Total_Asc
    ElseIf SortOrder = 3 And SortSequence = 2 Then
            Sort_Total_Desc
    End If

    Exit Sub

ErrorHandler:
    MsgBox "Something went wrong!", vbCritical
End Sub

     

Share

Resources