Check that the date is correct on a Userform prior to adding to spreadsheet

Is the user actually going to enter the date?

No, the user will not be entering the second date (previous day). My idea behind this is to try to prevent duplicate and skipped dates on the spreadsheet, an issue that has become a rather large problem when I return from a vacation as I am the one administrating and correcting errors on the spreadsheet.

If that's the case can't you just find the last date and add the appropriate number of days?

Originally, I was trying to force the users to think about the date they enter, obviously a fail for me right now

Thanks for the idea Norie, for some reason doing "Yesterdays Date" that way hadn't occurred to me. A case of trying to make something simple more difficult than it needs to be by me again.

I have added the following code for the "Todays Date" combobox and the "Yesterdays Date" textbox (activating the locked option for it)

Private Sub Next_Date()
  With Sheets("Midnights")
    Me.cboOperator.SetFocus
    Me.cboMidDate.Value = Format(Date - 1, "Medium Date")
    txtMidDate2.Value = Format(.Range("B" & .Rows.Count).End(xlUp).Value, "dd-mmm-yy")
  End With
End Sub

I would still like to deactivate the cboMidDate line of code and have the txtMidDate2 be a check on its value to ensure that the user is entering the correct date. That way if a date is missed, they are made to enter it before the current date. In other words, the "Todays Date" combobox is never more than 1 day greater than "Yesterdays Date" textbox.

The updated spreadsheet is below.
CheckDate.xlsm

I have done some experimenting and decided upon leaving the "Yesterdays Date" on the userform but trying to have the cboMidDate check itself to see if it is +1 day by using the coding below and having it highlight the combobox background red (that part isn't necessary but nice to have imo).

Private Sub cboMidDate_Check()
  If cboMidDate.ListIndex <> cboMidDate.ListCount + 1 Then
    cboMidDate.SetFocus
    cboMidDate.BackColor = vbRed
    MsgBox "Please check the date and try again"
    Exit Sub
  End If
End Sub

But I keep getting an "Invalid Property Value" error message every time I click add. The only way to shut down the userform after this is to use the stop in the VBA window.

EDIT - If I move the code to the Private Sub cmdAdd_Click section of coding, I keep getting my msgbox regardless of whether or not the date is +1 day or plus anything.

So far the best solution that I've come up with on my own for this is to take the Sub Next_Date code, alter it some, place it in the Sub Userform_Initialize and made sure to delete the Sub Next_Date coding entirely so that it looks like the following –

  cboOperator.SetFocus
  cboMidDate.Value = Format(Date - 1, "Medium Date")
With Sheets("Midnights")
  txtMidDate2.Value = Format(.Range("B" & .Rows.Count).End(xlUp).Value, "dd-mmm-yy")
End With

I added the following coding to the Sub cmdAdd_Click to try to make the end user aware –

Dim Response
Response = MsgBox("Was the Midnight Report for yesterday entered?", vbYesNo)
    If Response = vbNo Then Exit Sub

And I changed a couple of lines of code in the clear userform commands to eliminate the “Invalid Property Value” error I kept getting and hopefully to “fill out” the form for a little clarity for the end user–

Me.cboMidDate.Value = Format(Date - 1, "Medium Date")

Me.cboOperator.SetFocus

BTW, just so you all know, the code in red is what was causing my error.

I still would like for a msgbox error to popup if "Todays Date" is greater than "Yesterdays Date" plus 1 day. I'd love some ideas/help on figuring that out as my current knowledge of VBA is limited to what I've been picking up and learning.