Page 4 of 4 FirstFirst ... 234
Results 31 to 34 of 34

Thread: Date data validation formula

  1. #31
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    Sorry, I kind of forget what you really mean by occur within 7 days, does it include the same day?
    So if green room X with green room Y occur in the same day it's not a conflict?
    Yes that's right. It's basically use of some equipment related to that room so it's not a conflict at all, can be used at anytime separate.

  2. #32
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Ok, so the conflict only happens if for the same second criteria (x,y or z) user pick whole venue & any colored room within 7 days?
    If user doesn't pick whole venue then there is no situation that will cause conflict, is it correct?

    Another thing, is it possible that the user inserting duplicate entries? do you need to prevent it?

  3. #33
    New Member
    Join Date
    Sep 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    Ok, so the conflict only happens if for the same second criteria (x,y or z) user pick whole venue & any colored room within 7 days?
    If user doesn't pick whole venue then there is no situation that will cause conflict, is it correct?

    Another thing, is it possible that the user inserting duplicate entries? do you need to prevent it?

    Yep conflict if they have the same criteria within 7 days. Conflict for whole venue with the same criteria as any other room within 7 days.

    If there's no whole venue used within the 7 days then yes, no situation will cause conflict unless somebody tries to select the exact same venue and criteria e.g. Green Room X for 2nd Oct and Green Room X for 3rd Oct would conflict. Any other combination of room and criteria is fine.

    Duplicate entries isn't a likely issue, there are further columns where they specify the person who has booked it etc so it would be obvious if there was an issue there. And it still isn't very many events per month.

  4. #34
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Ok, try this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge <> 1 Then Exit Sub
        
        If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
        
            If Target.Offset(, 1) = "" Or Target.Offset(, 2) = "" Then
                MsgBox "You need to pick a Venue and a criteria"
                Application.EnableEvents = False
                Target.ClearContents
                Application.EnableEvents = True
                Exit Sub
            End If
    
            
            Dim c As Range, flag As Boolean, tx As String, crt As String
            If IsDate(Target) Then
                flag = True: tx = "": ven = Target.Offset(, 1).Value: crt = Target.Offset(, 2).Value
                For Each c In Range("A1:A20")
                    If Len(c) > 0 And IsDate(c) And c.Address <> Target.Address Then
                        If c.Offset(, 2) = crt And (ven = "Whole Venue" Or c.Offset(, 1) = "Whole Venue" Or c.Offset(, 1) = ven) Then
                            If Abs(DateDiff("d", CDate(Target), CDate(c))) < 7 Then
                                tx = tx & c & " : " & c.Offset(, 1) & " : " & crt & vbLf
                                flag = False
                            End If
                        End If
                    End If
                Next
                
                If flag = False Then
                        MsgBox "Pick another date." & vbLf & "This has been allocated:" & vbLf & tx, vbCritical
                        Application.EnableEvents = False
                        Target.ClearContents
                        Application.EnableEvents = True
                        Exit Sub
                End If
            Else
                MsgBox "You need to insert a date"
                        Application.EnableEvents = False
                        Target.ClearContents
                        Application.EnableEvents = True
    
            End If
            
        End If
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •