VBA Code to make sure that Date (Input Box result) is in sequence with the previous date from the previous row

[ArleneJo]

Hi,

I've been trying to come up with a code to make sure that any dates entered by user are in sequence with the previous date above each row. If user entered a wrong date, a message will appear and will clear out the cell content then will prompt another input box to enter the correct date. I tried to use the IF THEN statement with DATEDIFF function but for some reason the formatting messes it up. Debugging points me out to the IF THEN statement. In code below, you will notice that I also try to automate the day input to right column based on the input date. Here's the code:

Dim varInputDate As Variant
Dim lngERow As Long
Dim lngPRow As Long
Dim dteMyPreviousInputDate As Date


lngPRow = Range("B" & Rows.Count).End(xlUp).Row
lngERow = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
dteMyPreviousInputDate = Range("B" & lngPRow).Value
varInputDate = Range("B" & lngERow).Value


Do
varInputDate = InputBox("Please enter the settlement date", "Settlement Date", "dd/mm/yyyy")


'cancel pressed
If StrPtr(varInputDate) = 0 Then Exit Sub


If Not IsDate(varInputDate) Then MsgBox "Please enter a valid date format dd/mm/yyyy", 16, "Invalid Date"


Loop Until IsDate(varInputDate)




With Range("B" & lngERow)
    .Value = DateValue(varInputDate)
    .NumberFormat = "dd/mm/yyyy"
    .Select
End With


If DateDiff("d", varInputDate, dteMyPreviousInputDate) <> 1 Then
    MsgBox "Invalid Date"
    Range("B" & lngERow).ClearContents
    Range("A" & lngERow).ClearContents
  
Do
varInputDate = InputBox("Please enter the settlement date", "Settlement Date", "dd/mm/yyyy")
lngERow = Range("B" & Rows.Count).End(xlUp).Offset(1).Row


'cancel pressed
If StrPtr(varInputDate) = 0 Then Exit Sub


If Not IsDate(varInputDate) Then MsgBox "Please enter a valid date format dd/mm/yyyy", 16, "Invalid Date"


Loop Until IsDate(varInputDate)
End If




With Range("B" & lngERow)
    .Value = DateValue(varInputDate)
    .NumberFormat = "dd/mm/yyyy"
    .Select
End With




With Range("A" & lngERow)
    .HorizontalAlignment = xlLeft
    .NumberFormat = "dddd"
    .Value = DateValue(varInputDate)
End With


End Sub

 

[John_w]

Try something like this:

Public Sub Test()

    Dim varInputDate As Variant
    Dim lngERow As Long
    Dim lngPRow As Long
    Dim dteMyPreviousInputDate As Date
    
    lngPRow = Range("B" & Rows.Count).End(xlUp).Row
    lngERow = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
    dteMyPreviousInputDate = Range("B" & lngPRow).Value
    
    Do
        varInputDate = InputBox("Please enter the settlement date", "Settlement Date", "dd/mm/yyyy")
        
        'cancel pressed
        If varInputDate = "" Then Exit Sub
        
        If Not IsDate(varInputDate) Then
            MsgBox "Please enter a valid date format dd/mm/yyyy", vbCritical, "Invalid Date"
        ElseIf CDate(varInputDate) <> dteMyPreviousInputDate + 1 Then
            Debug.Print CDate(varInputDate)
            MsgBox "The entered date must be 1 day after the previous date (" & dteMyPreviousInputDate & ")"
        End If
    
    Loop While Not IsDate(varInputDate) Or varInputDate <> dteMyPreviousInputDate + 1
    
    Range("B" & lngERow).Value = CDate(varInputDate)

End Sub

But if the next date should be 1 day after the previous date why do you need the user to input it? You could compute the next date by adding 1 to the previous date.

 

[ArleneJo]

Hi John_W! it works! thanks so much. I still have so much to learn. As to your question, I need a user to input the new date as I dont know of any other way on how to trigger it. Any thoughts?

 

[John_w]

This puts the date of the next day in the column B cell below the previous date.

Public Sub NextDate()

    Dim lr As Long
    Dim lastDate As Date
    
    With ActiveSheet
        lr = .Range("B" & Rows.Count).End(xlUp).Row
        lastDate = .Cells(lr, "B").Value
        If IsDate(lastDate) Then .Cells(lr + 1, "B").Value = lastDate + 1
    End With

End Sub