VBA to replace Data Validation

[Lonilccr]

I love this forum and have found solutions for- and learned- so many things that are priceless to my excel creations!
I'm planning to convert some spreadsheet tools through an excel compiler program but there are limitations with the data validation formulas for this program. I have liberally used data validation to control the behavior of my tool and am hoping a macro is a good alternative workaround, but my VBA skills are pretty limited at this point.
I have this formula which controls the allowable dates to be entered into the field.
=OR(AND(ISBLANK($R7),$Y7>$E7,$Y7<=TODAY()), AND(R7<>"", S7<>"", $Y7>$E7,$Y7<=TODAY()))
All of the cell references are various date fields entered in the row of data and the TODAY reference is to prevent a future date.

 

[Micron]

the allowable dates to be entered into the field

This might make some of the formula tests redundant so it might be best if you elaborated on what that means or would look like. I think I see at least one redundancy now; i.e. I don't think R7 can be blank and be "" at the same time. Perhaps if you posted data that shows some relevant data as well as entries that are allowed and not allowed it would help. Also, what user action would trigger the validation and is it always going to be those cells you posted?

 

[Lonilccr]

This might make some of the formula tests redundant so it might be best if you elaborated on what that means or would look like. I think I see at least one redundancy now; i.e. I don't think R7 can be blank and be "" at the same time. Perhaps if you posted data that shows some relevant data as well as entries that are allowed and not allowed it would help. Also, what user action would trigger the validation and is it always going to be those cells you posted?

Thank you for responding! I hope I've successfully created a mini-sheet and given enough information. All actions are based on dates. I added screen snips of actions that trigger the validation rule as well.

Book1 Mr. Excel example.xlsx
	A	B	C	D
1	Start Date	temporary stop date	Restart date	End date (target column for data validation error)
2	11/1/23	11/7/23	11/9/23	11/20/23
3	11/1/23	11/7/23
4	11/1/23
5	11/1/23
6
7
Sheet 1

Cells with Data Validation
Cell	Allow	Criteria
A1:D1	Any value
A2:A7	Date	between TODAY()-100 and TODAY()
B2:B7	Any value
C2:C7	Any value
D2:D7	Custom	=OR(AND(ISBLANK($B2),$D2>$A2,$D2<=TODAY()), AND(B2<>"", C2<>"", $D2>$A2,$D2<=TODAY()))

The column references are different from original post. =OR(AND(ISBLANK($B2),$D2>$A2,$D2<=TODAY()), AND(B2<>"", C2<>"", $D2>$A2,$D2<=TODAY()))
And in narrative terms the rules are:
*If the Temporary Stop Date (B2) is blank, then the date entered into End Date (D2) must be later than the Start Date (A2) and NOT later than today's date
*If the Temporary Stop Date (B2) is NOT blank, but Restart Date (C2) IS blank, this will trigger the error. In other words if there is a Temporary Stop date, there must be a Restart date to later have an End Date (D2)
*If Temp Stop Date (B2) and Restart Date (C2) BOTH have dates, then further, the End Date (D2) must be both later than the Start Date (A2) and not later than today's date

 

[Skyybot]

I think that this may fulfill your needs. Please test on a copy of your Workbook as unexpected results may occur.
Place this code in ThisWorkbook

Sub ValidateDate(ByVal Target As Range, sDate As Range, ByVal teDate As Range, ByVal rDate As Range, ByVal eDate As Range)
Dim wb As Workbook, sht As Worksheet
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
If Not IsDate(Target) And Not IsEmpty(Target) Then
    MsgBox "Please enter a valid Date", vbCritical, "Error"
    Target.ClearContents
    Target.Select
    Exit Sub
End If
Select Case Target.Address
    Case Is <> sDate.Address
        Select Case Target.Address
            Case Is = teDate.Address
                If IsEmpty(sDate) And Not IsEmpty(Target) Then
                    MsgBox "Please enter a Start Date first.", vbCritical, "Error"
                    Target.ClearContents
                    sDate.Select
                    Exit Sub
                End If
                If Target < sDate And Not IsEmpty(Target) Then
                    MsgBox "Temporary Stop Date must be later than Start Date.", vbCritical, "Error"
                    Target.ClearContents
                    Target.Select
                    Exit Sub
                End If
            Case Is = rDate.Address
                If IsEmpty(sDate) And Not IsEmpty(Target) Then
                    MsgBox "Please enter a Start Date first.", vbCritical, "Error"
                    Target.ClearContents
                    sDate.Select
                    Exit Sub
                End If
                 If IsEmpty(teDate) And Not IsEmpty(Target) Then
                    MsgBox "Please enter a Temporary Stop Date first.", vbCritical, "Error"
                    Target.ClearContents
                    teDate.Select
                    Exit Sub
                End If
                If Target < teDate And Not IsEmpty(Target) Then
                    MsgBox "Restart Date must be later than Temporary Stop Date.", vbCritical, "Error"
                    Target.ClearContents
                    Target.Select
                    Exit Sub
                End If
            Case Is = eDate.Address
                 If IsEmpty(sDate) And Not IsEmpty(Target) Then
                    MsgBox "Please enter a Start Date first.", vbCritical, "Error"
                    Target.ClearContents
                    sDate.Select
                    Exit Sub
                End If
                 If IsEmpty(teDate) And Not IsEmpty(Target) Then
                    MsgBox "Please enter a Temporary Stop Date first.", vbCritical, "Error"
                    Target.ClearContents
                    teDate.Select
                    Exit Sub
                End If
                If IsEmpty(rDate) And Not IsEmpty(Target) Then
                    MsgBox "Please enter a Restart Date first.", vbCritical, "Error"
                    Target.ClearContents
                    rDate.Select
                    Exit Sub
                End If
                If Target < rDate And Not IsEmpty(Target) Then
                    MsgBox "End Date must be later than Restart Date.", vbCritical, "Error"
                    Target.ClearContents
                    Target.Select
                    Exit Sub
                End If
        End Select
    Case Else
        Exit Sub
End Select
End Sub

And place this code in the Sheet with your data

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Column
    Case 1
        ThisWorkbook.ValidateDate Target, Target, Target.Offset(0, 1), Target.Offset(0, 2), Target.Offset(0, 3)
    Case 2
        ThisWorkbook.ValidateDate Target, Target.Offset(0, -1), Target, Target.Offset(0, 1), Target.Offset(0, 2)
    Case 3
        ThisWorkbook.ValidateDate Target, Target.Offset(0, -2), Target.Offset(0, -1), Target, Target.Offset(0, 1)
    Case 4
        ThisWorkbook.ValidateDate Target, Target.Offset(0, -3), Target.Offset(0, -2), Target.Offset(0, -1), Target
    Case Else
        Exit Sub
End Select
End Sub

 

[Lonilccr]

Thank you! I can definitely work with this