Multi Option Message Box Not stopping at the chosen selection

[FrancisM]

I have a multi option Message box, that has a main box, & a sub message box. The Main box has the standard Yes/No responses. The Yes goes to the sub box, & the NO is supposed to go back to the previous worksheet. However it is going to the sub worksheet. The problem I am having with the sub box is that it is going to the last option & not stopping at the chosen selection. The other problem is if you choose Cancel, it does not go back to the original worksheet that you were on.

The code shown below is from Google, & pieces that I have used elsewhere.

Sub Walk_In_Training_Data_Entry_Quarter1_Quarter2()
 Dim MyValue As Integer
'MsgBox prompt:="Continue to Walk In Training Data Entry?", Title:="Referral Workbook Data Entry"
i = MsgBox("Continue to Walk In Training Data Entry 1st Quarter?", vbYesNo, "Referral Workbook Data Entry")
'First message shows in the body of the box, message 2 shows at the top of the box.
MyValue = InputBox("Only Click Ok or Cancel after your Selection!!!!!!!" & vbCrLf & _
                           "Enter 1 for October" & vbCrLf & _
                           "Enter 2 for November" & vbCrLf & _
                           "Enter 3 for December", "Walk In Training Data Entry")
    Do
        If (MyValue = 1) Or (MyValue = 2) Or (MyValue = 3) Then
            Exit Do
        Else
            MsgBox "You have not made a valid entry.  Please try again."
        End If
    Loop
'Code to Execute When Condition = value_1
Select Case Condition
Case value_1
             If ActiveSheet.CodeName = "Sheet52" Then
       ' The message below only shows when you are on the active sheet.
                MsgBox "You are already on October Walk In Training Data Entry!", vbInformation
            Else
                Sheets("WI_OT_1ST").Activate
                Range("A1").Select
                End If
                End Select
        Select Case Ans
        Case Else
        End Select
'Code to Execute When Condition = value_2
Select Case Condition
Case value_2
             If ActiveSheet.CodeName = "Sheet53" Then
       ' The message below only shows when you are on the active sheet.
                MsgBox "You are already on October Walk In Training Data Entry!", vbInformation
            Else
                Sheets("WI_NT_1ST").Activate
                Range("A1").Select
                End If
                End Select
        Select Case Ans
        End Select
'Code to Execute When Condition = value_3
Select Case Condition
Case value_2
             If ActiveSheet.CodeName = "Sheet54" Then
       ' The message below only shows when you are on the active sheet.
                MsgBox "You are already on December Walk In Training Data Entry!", vbInformation
            Else
                Sheets("WI_DT_1ST").Activate
                Range("A1").Select
                End If
                End Select
        Select Case Ans
        End Select
End Sub

I know I am missing something, But I don't know what, & have not found an answer on Google.

 

[FrancisM]

Noticed that there are 2 things that I mentioned in my initial post & I did not address. 1. The fact that NO on the main form is supposed to go back to the previous worksheet. However it is going to the sub worksheet. 2. The other problem is if you choose Cancel on the sub form, it does not go back to the original worksheet that you were on. I will continue working on a solution, post the solution here. If you should find a solution prior to me posting it, please let me know.

 

[FrancisM]

I have noticed that the NO option in the main message box doesn’t have any directions and the Canceloption in the sub message box also has no directions. I have tried putting exit,stop code between the message box & the balance of the code. When I do thatI get a compile error message (Argument not optional). I was wondering Ifeither the No or Cancel buttons are selected, can the message box be made to disappear.<o></o>

 

[Fluff]

Try adding the line in red

    i = MsgBox("Continue to Walk In Training Data Entry 1st Quarter?", vbYesNo, "Referral Workbook Data Entry")
    [COLOR=#ff0000]If Not i = vbYes Then Exit Sub[/COLOR]

 

[FrancisM]

Thank You, that solves the problem with the No button. The sub message box has a cancel button, & I am trying to also have that message box disappear. I have tried varies pieces of code involving cancel, & have received various error messages. Could some one explain where the code is placed, and why the sub buttons don't show up in the code?

 

[Fluff]

Is this what your after

     Dim MyValue
     Dim i As String
     
    'MsgBox prompt:="Continue to Walk In Training Data Entry?", Title:="Referral Workbook Data Entry"
    i = MsgBox("Continue to Walk In Training Data Entry 1st Quarter?", vbYesNo, "Referral Workbook Data Entry")
    If Not i = vbYes Then Exit Sub
    
    'First message shows in the body of the box, message 2 shows at the top of the box.
    Do
        MyValue = Application.InputBox("Only Click Ok or Cancel after your Selection!!!!!!!" & vbCrLf & _
                               "Enter 1 for October" & vbCrLf & _
                               "Enter 2 for November" & vbCrLf & _
                               "Enter 3 for December", "Walk In Training Data Entry")
        If MyValue = False Then
            Exit Sub
        ElseIf (MyValue = 1) Or (MyValue = 2) Or (MyValue = 3) Then
            Exit Do
        Else
            MsgBox "You have not made a valid entry.  Please try again."
        End If
    Loop

 

[FrancisM]

I placed the code that you provided, I think in the locationthat you suggested. When I ran the code, I received a Compile Error: Block if withoutEnd if. I am not clear about the block if & the end if. I have included theentire code, since it has been a few postings since I originally post it.

Sub Walk_In_Training_Data_Entry_Quarter1_Quarter2()
     Dim MyValue As Integer
    'MsgBox prompt:="Continue to Walk In Training Data Entry?", Title:="Referral Workbook Data Entry"
    i = MsgBox("Continue to Walk In Training Data Entry 1st Quarter?", vbYesNo, "Referral Workbook Data Entry")
    'Removes main message box, if No is pressed.
    If Not i = vbYes Then Exit Sub
    'First message shows in the body of the box, message 2 shows at the top of the box.
    MyValue = InputBox("Only Click Ok or Cancel after your Selection!!!!!!!" & vbCrLf & _
                               "Enter 1 for October" & vbCrLf & _
                               "Enter 2 for November" & vbCrLf & _
                               "Enter 3 for December" & vbCrLf & _
                               "Enter 4 for January" & vbCrLf & _
                               "Enter 5 for February" & vbCrLf & _
                               "Enter 6 for March", "Walk In Training Data Entry")
        If MyValue = False Then
            Exit Sub
                                      
        Do
            If (MyValue = 1) Or (MyValue = 2) Or (MyValue = 3) Or (MyValue = 4) Or (MyValue = 5) Or (MyValue = 6) Then
                Exit Do
                If MyValue = False Then
            Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            Else
                MsgBox "You have not made a valid entry.  Please try again."
            End If
            Do
        Loop
    'Code to Execute When Condition = value_1
    Select Case MyValue
        Case 1
                     If ActiveSheet.CodeName = "Sheet52" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_OT_1ST").Activate
                        Range("A1").Select
                    End If
        'Code to Execute When Condition = value_2
        Case 2
                     If ActiveSheet.CodeName = "Sheet53" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_NT_1ST").Activate
                        Range("A1").Select
                    End If
        
        'Code to Execute When Condition = value_3
        Case 3
                     If ActiveSheet.CodeName = "Sheet54" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on December Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_DT_1ST").Activate
                        Range("A1").Select
                End If
       'Code to Execute When Condition = value_4
       Case 4
                     If ActiveSheet.CodeName = "Sheet55" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on January Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_JT_2ND").Activate
                        Range("A1").Select
                End If
     'Code to Execute When Condition = value_5
       Case 5
                     If ActiveSheet.CodeName = "Sheet56" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on February Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_FT_2ND").Activate
                        Range("A1").Select
                End If
    'Code to Execute When Condition = value_6
       Case 6
                     If ActiveSheet.CodeName = "Sheet57" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on March Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_MT_2ND").Activate
                        Range("A1").Select
                End If
    End Select
End Sub

 

[Fluff]

The entire is this

Sub Walk_In_Training_Data_Entry_Quarter1_Quarter2()
     
     Dim MyValue
     Dim i As String
     
    'MsgBox prompt:="Continue to Walk In Training Data Entry?", Title:="Referral Workbook Data Entry"
    i = MsgBox("Continue to Walk In Training Data Entry 1st Quarter?", vbYesNo, "Referral Workbook Data Entry")
    If Not i = vbYes Then Exit Sub
    
    'First message shows in the body of the box, message 2 shows at the top of the box.
    Do
        MyValue = Application.InputBox("Only Click Ok or Cancel after your Selection!!!!!!!" & vbCrLf & _
                               "Enter 1 for October" & vbCrLf & _
                               "Enter 2 for November" & vbCrLf & _
                               "Enter 3 for December", "Walk In Training Data Entry")
        If MyValue = False Then
            Exit Sub
        ElseIf (MyValue = 1) Or (MyValue = 2) Or (MyValue = 3) Then
            Exit Do
        Else
            MsgBox "You have not made a valid entry.  Please try again."
        End If
    Loop    'Code to Execute When Condition = value_1
    Select Case MyValue
        Case 1
                     If ActiveSheet.CodeName = "Sheet52" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_OT_1ST").Activate
                        Range("A1").Select
                    End If
        'Code to Execute When Condition = value_2
        Case 2
                     If ActiveSheet.CodeName = "Sheet53" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_NT_1ST").Activate
                        Range("A1").Select
                    End If
        
        'Code to Execute When Condition = value_3
        Case 3
                     If ActiveSheet.CodeName = "Sheet54" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on December Walk In Training Data Entry!", vbInformation
                    Else
                        Sheets("WI_DT_1ST").Activate
                        Range("A1").Select
                End If
    End Select
End Sub

 

[FrancisM]

Thank you. I copied the pieces of code that you provided& I had problems. When I copied all of your code & added the additionalthree months it worked. As usual You guys are fantastic.<o></o>

 

[Fluff]

Glad to help & thanks for the feedback