If Statements Not Progressing Through

[reberryjr]

Can someone tell me why the first code snippet won't progress through? When I click No on the message box from the first if statement, the code isn't progressing through to the next if statement.

Private Sub cmd_Submit_Click()
Dim cGender, cEntryType, cPymtFreq, cPymtStatus, cStatus, cPymtMethod, cRefCat, cFacilitator, cPymtReason, cBiosClientID, cBiosNickname, cStatsClientID As Range
Dim ws1, ws2, ws3, ws4, ws5, ws6, ws7 As Worksheet
Dim Verify As Integer
Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True
Set ws1 = ThisWorkbook.Sheets("Management")
Set ws2 = ThisWorkbook.Sheets("Summaries")
Set ws3 = ThisWorkbook.Sheets("Bios")
Set ws4 = ThisWorkbook.Sheets("Stats")
Set ws5 = ThisWorkbook.Sheets("Pymt Tracker")
Set ws6 = ThisWorkbook.Sheets("Financials")
Set ws7 = ThisWorkbook.Sheets("Variables")
LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row
LastRow3 = ws3.Range("E" & Rows.Count).End(xlUp).Row
LastRow4 = ws4.Range("C" & Rows.Count).End(xlUp).Row
LastRow5 = ws5.Range("C" & Rows.Count).End(xlUp).Row
LastRow6 = ws6.Range("D" & Rows.Count).End(xlUp).Row
LastRow7 = ws7.Range("J" & Rows.Count).End(xlUp).Row
If Len(Me.txt_DPDate.Value) = 0 And Len(Me.txt_DPAmt.Value) = 0 And Len(Me.txt_DPPaid.Value) = 0 Then
    Verify = MsgBox("Did the Client sign up for a DP?", vbYesNo)
    If Response = vbYes Then Me.txt_DPDate.SetFocus
Exit Sub
End If
If (Not Len(Me.txt_CTIStart.Value) = 0 Or Not Len(Me.txt_CTI1stPymt.Value) = 0 Or (Me.txt_CTIPymtAmt.Value) > 0 Or Not Len(Me.cobo_CTIFreq.Value) = 0 Or _
    (Me.txt_CTIAmtPaid.Value) > 0) And (Len(Me.txt_CTIStart.Value) = 0 Or Len(Me.txt_CTI1stPymt.Value) = 0 Or Len(Me.txt_CTIPymtAmt.Value) = 0 Or Len(Me.cobo_CTIFreq.Value) = 0) Then
        MsgBox "Please complete the partial information in the CT-I section."
        If Response = vbOK Then Me.txt_CTIStart.SetFocus
Exit Sub
End If

When I use select case for other validations, it steps through as desired.

Select Case True
Case Me.txt_Notes = ""
    MsgBox "Please enter pertinent notes about the Client."
    If Response = vbOK Then Me.txt_Notes.SetFocus
    Exit Sub
    IsOk = False
Case Me.cobo_TotalPymtMethod = ""
    MsgBox "Please select the method the Client used to tender funds."
    If Response = vbOK Then Me.cobo_TotalPymtMethod.SetFocus
    Exit Sub
    IsOk = False
End Select

 

[RoryA]

If this condition is met:

If Len(Me.txt_DPDate.Value) = 0 And Len(Me.txt_DPAmt.Value) = 0 And Len(Me.txt_DPPaid.Value) = 0 Then

(which I assume it is, since you get the msgbox), then you have Exit Sub running regardless of the msgbox choice.

I'd also note that throughout the code, you are either not assigning the result of Msgbox to anything, or are assigning it to a variable called Verify, but then testing a Response variable, which has no value.

 

[reberryjr]

Not sure that I understand. The Case Statements have the exit sub also, but continue to progress through. I'm not sure why it would be different for the If statement block.

 

[RoryA]

I'll break down your Select Case:

Select Case True
Case Me.txt_Notes = "" '<- If the textbox is empty, then next part executes
    MsgBox "Please enter pertinent notes about the Client." '<- Message is put up but the response is not stored anywhere.
    If Response = vbOK Then Me.txt_Notes.SetFocus '<- this will never be true as Response was not assigned a value
    Exit Sub '<-routine will exit if the textbox was blank, no matter what you answered to the message box.
    IsOk = False ' this line will never process.

and similarly your If block:

If Len(Me.txt_DPDate.Value) = 0 And Len(Me.txt_DPAmt.Value) = 0 And Len(Me.txt_DPPaid.Value) = 0 Then ' <- if all three textboxes are empty then
    Verify = MsgBox("Did the Client sign up for a DP?", vbYesNo) ' <- Put up a message and store result in Verify variable.
    If Response = vbYes Then Me.txt_DPDate.SetFocus '<- This will never be true since you didn't assign anything to the Response variable
Exit Sub  ' <- if all three textboxes are empty this line will execute regardless of what you answered to the message box, and the routine will stop
End If

 

[reberryjr]

Ok, so I'm feeling really dense here. With the select case statement, the first snippet is met and I correct it, hit the cmd button again, and then the 2nd condition is evaluated. I'm ok with the User having to click the cmd button in between correcting data events. (Maybe they'll learn to enter the data correctly, the first time?...lol) I would love for the focus to get set to the right box, but at the end of the day, it's not critical.

With respect to the If statement, I'm really unsure what you're telling me. I snagged that code off of the web and it seemed as though vbYes was what was assigned to the Response variable.

 

[RoryA]

No, you assigned it to a variable called Verify when you did this:

Verify = MsgBox(...)

Change that to:

Response = MsgBox(...)

and the next line will work, but your code is still going to exit the routine on the next line. It looks to me like you should only exit if the answer is Yes? So more like this:

If Len(Me.txt_DPDate.Value) = 0 And Len(Me.txt_DPAmt.Value) = 0 And Len(Me.txt_DPPaid.Value) = 0 Then
    Response = MsgBox("Did the Client sign up for a DP?", vbYesNo)
    If Response = vbYes Then
        Me.txt_DPDate.SetFocus
        Exit Sub
    End If
End If

 

[reberryjr]

Ok...that works. So that I understand it, for future use...I'll probably get the terminology wrong, but...

Changing Verify to Response here, allows me to tell the code what to do if the response is this or that, and Response is the variable?

    Response = MsgBox("Did the Client sign up for a DP?", vbYesNo)

 

[RoryA]

Yes. Msgbox is a function that returns a value based on which button was pressed, and you store that result in a variable. It doesn't matter too much what that variable is called as long as you are consistent. So either:

    Response = MsgBox("Did the Client sign up for a DP?", vbYesNo)
    If Response = vbYes Then

or:

    Verify = MsgBox("Did the Client sign up for a DP?", vbYesNo)
    If Verify = vbYes Then

are fine.

 

[reberryjr]

Thank you for the edu-mi-cation!

 

[RoryA]

Glad to help. Just as an additional point, you don't really even need a variable here as you are only interested in a Yes/No, so you can use the Msgbox result directly like this:

If MsgBox("Did the Client sign up for a DP?", vbYesNo) = vbYes Then