Userform Next button doesn't work when if-statement validation check fails and prompts a msgbox

Question

I have a userform with a Next button. In the Next_Click sub, I have a long if statement that checks every field for completeness and valid data.

There are two date boxes that have today's date in them by default as a way to show the user the necessary date format. But since it's pre-filled, I thought people might forget to put in the correct date, so if the date is still today's date when the Next button is clicked, a msgbox pops up asking if the user meant for the date to be today's date.

The idea is if the user says no, then they get to go back and change the date, and then when they click Next again, the same validation check if-statement will run and then they can go on to the next page. If they say yes, then it's supposed to just continue with the rest of the validation check and go on to the next page.

BUT it's like the msgbox popping up precludes the userform from switching to the next page (really a separate userform because I didn't know about the multi-page thing when I did this). I tried putting invalid data in another textbox, and when my msgbox popped up telling me to re-enter the data, I was able to enter the data and then go to the next page. So it's something wrong with my date textboxes, I guess. I've pasted below everything that applies to the date textbox in the Next_Click sub.

Edit: I should also mention that if I put in a date other than today, the Next button works just fine.

1     Private Sub IB_Next_Click()
2     'Data validation
3                 
4             ElseIf Me.IB_LoanDate.Value = "" Then
5                 blahAnswer = MsgBox("Please enter a loan date.")
6                 Me.IB_LoanDate.SetFocus
7                 Exit Sub
8             ElseIf IsDate(Me.IB_LoanDate.Value) = False Then
9                 blahAnswer = MsgBox("Please enter a valid date.", , "Invalid Entry")
10                Me.IB_LoanDate.SetFocus
11                 Exit Sub
12            ElseIf CDate(Me.IB_LoanDate.Value) = VBA.Date Then
13                loanDateAnswer = MsgBox("The loan date is still set to the default of today's date. Was that intentional?", vbYesNo, "Validation Check")
14                    If loanDateAnswer = vbNo Then
15                        Me.IB_LoanDate.SetFocus
16                        Exit Sub
17                    End If
18            ...
19            Else
20            ...
21            LoanWiz_IB.Hide
22            LoanWiz_IntTerms.Show
23        End If
24    End Sub

If I click No, it does Exit Sub like it's supposed to, but then the Next button doesn't work after I fix the date. If I click Yes, it's like it stops reading the code after it reads line 17. Any suggestions on making it read the rest of the code?

Tim Williams Tim Williams · Accepted Answer · 2019-10-19T06:16:54

Those ElseIf blocks and the final Else block are mutually exclusive - the last Else block doesn't run if any of the other blocks execute. So you don't need that last Else, and the Hide and Show lines should be outside if the If

 Private Sub IB_Next_Click()
 'Data validation

          ElseIf Me.IB_LoanDate.Value = "" Then
              blahAnswer = MsgBox("Please enter a loan date.")
              Me.IB_LoanDate.SetFocus
             Exit Sub
         ElseIf IsDate(Me.IB_LoanDate.Value) = False Then
             blahAnswer = MsgBox("Please enter a valid date.", , "Invalid Entry")
            Me.IB_LoanDate.SetFocus
             Exit Sub
        ElseIf CDate(Me.IB_LoanDate.Value) = VBA.Date Then
            loanDateAnswer = MsgBox( _
         "The loan date is still set to the default of today's date. Was that intentional?", _
          vbYesNo, "Validation Check")
            If loanDateAnswer = vbNo Then
                Me.IB_LoanDate.SetFocus
                Exit Sub
            End If
        ...
        End If
        ...
        LoanWiz_IB.Hide
        LoanWiz_IntTerms.Show

End Sub

Userform Next button doesn't work when if-statement validation check fails and prompts a msgbox

1 Answers