VBA Help with DATE Input Box & Error Handling

[OxFACTOR]

Very helpful forum here! Thanks to all who assist!

I'm having trouble coding a Auto Open routine in an Excel sheet ... the objective is:

1. Upon opening the sheet VBA executes and an input box opens asking the user to input a date
2. This date entry is required; so, if the input box is empty or "cancel" is clicked an error is displayed & the code loops back to the original input box asking for the date
3. Once the date is entered successfully, the code checks the format of the date & displays a message box showing the date entered.
4. In this "confirmation" message box, the user either clicks "Yes" to accept or "No" to start over (which starts the VBA routine over)
5. If "Yes" is clicked, the date is placed in a cell on the sheet

My code so far is:

Sub Auto_Open()

    Dim I
    For I = 1 To 1 ' Loop 1 time.
        Beep  ' Sound a tone.
    Next I
    


Dim strDate As String
Dim EntryOK As Boolean

Do While EntryOK > 0
    strDate = InputBox("Please Enter the PRODUCTION REPORTING DATE as MM/DD/YYYY", "Production Reporting Date", Format(Now() - 1, "mm/dd/yyyy"))
    
    If IsDate(strDate) = 0 Then 'Checking if Date was entered
        MsgBox "Please enter a production date!", vbCritical
        
    Else
        If IsDate(strDate) Then
    strDate = Format(CDate(strDate), "mm/dd/yyyy")
    MsgBox "The PRODUCTION DATE you entered is " & strDate
        Else
    MsgBox "Wrong Date Format or Missing Date"
    End If
   
   
    End If
Loop
    Sheets("DAILY OPERATIONS SUMMARY").Range("e6") = strDate
End Sub

I'm stuck I a loop & I haven't figured out the "confirmation yes/no" piece. Please help!

 

[John_w]

Try this:

    Dim strDate As String
    Dim acceptDate As Integer
    
    Do
        Do
            strDate = InputBox("Please Enter the PRODUCTION REPORTING DATE as MM/DD/YYYY", "Production Reporting Date", Format(Date - 1, "mm/dd/yyyy"))
            If Not IsDate(strDate) Then MsgBox "Please enter a production date!", vbCritical
        Loop Until IsDate(strDate)
        strDate = Format(CDate(strDate), "mm/dd/yyyy")
        acceptDate = MsgBox("The PRODUCTION DATE you entered is " & strDate & vbNewLine & "Accept this date?", vbYesNo)
    Loop Until acceptDate = vbYes
    
    Sheets("DAILY OPERATIONS SUMMARY").Range("E6") = strDate

 

[OxFACTOR]

This works great & is much more compact vs. my last attempt! Thanks!