Multi Option Message Box Not stopping at the chosen selection

F

FrancisM

Board Regular
Joined
Apr 12, 2016
Messages
139
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.

Code: 
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Where are the values for Condition, value_1, value_2 etc. get their values from?
 
Upvote 0
FrancisM said:
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.

Code: 
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.
Click to expand...

The values come from the various work sheets.
 
Upvote 0
As far as I can see they don't get values from anywhere in the code you posted.
 
Last edited:
Upvote 0
I have never attempted any thing like this before, it has always been the basics. Do you have any suggestions to make it operational?
 
Upvote 0
What should the values of Condition, value_1, value_2 etc. be and where would the values come from?
 
Upvote 0
Thank you for being so patient with me. I have tried changing Value_1, 2, 3 to actual sheet numbers. I.e. "Enter 1" was changed to "Enter Sheet52 for October". When I ran the code I received a runtime error "13": Type mismatch. on
Code: 
MyValue = InputBox("Only Click Ok or Cancel after your Entry!!!!!!!" & vbCrLf & _
                           "Enter Sheet52 for October" & vbCrLf & _
                           "Enter Sheet53 for November" & vbCrLf & _
                           "Enter Sheet52 for December", "Walk In Training Data Entry")
Do you have any suggestions how to resolve this issue?
 
Upvote 0
Is this what you're after
Code: 
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 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
 
Upvote 0
Thank You. After studying what you wrote, it is hard to believe that it was simple errors. I guess that I was working on it, for so, I couldn't see the simple errors.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Excel OpenDialogBox then Transfer the information to cell
Replies
5
Views
210
Daniza Morla
D
I
  • Question
Move existing row of value to another selected row
Replies
1
Views
133
ipbr21054
I
J
  • Question
Vba code to display the 3 values contained in a specified column in a message box
Replies
1
Views
277
rlv01
R
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
170
bh24524
B
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
134
Tupe77
T

Forum statistics

Threads
1,215,045
Messages
6,122,840
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top