Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Date data validation formula

  1. #11
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,061
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    I added another feature in the code. If you pick a wrong date then it will pop up a message to pick another date & also show other relevant dates (which would be violated when you pick that specific date).

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge <> 1 Then Exit Sub
    
        If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
            Dim c As Range, flag As Boolean, tx As String
            If IsDate(Target) Then
                flag = True: tx = ""
                For Each c In Range("A1:A10")
                     
                    If Len(c) > 0 And IsDate(c) And c.Address <> Target.Address _
                    And (c.Offset(, 1) = "Whole venue" Or Target.Offset(, 1) = "Whole venue" Or _
                    c.Offset(, 1) = Target.Offset(, 1)) Then
                        If Abs(DateDiff("d", CDate(Target), CDate(c))) < 7 Then
                        tx = tx & c & " : " & c.Offset(, 1) & vbLf
                        flag = False
                        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.Activate
                        Target.ClearContents
                        Application.EnableEvents = True
                        Exit Sub
                End If
            End If
            
        End If
    End Sub
    

  2. #12
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Hi! Sorry I didn't see this update sooner, that's a really cool change. I'll give it a try!

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

    Default Re: Date data validation formula

    I have encountered a problem!

    If I have a "Green room" event booked for 14/11/2019 it still lets me book a "full venue" event for 15/11/2019. Even though the Green room is already booked. It works the other way around e.g. if the whole venue event is booked first it won't let the other one be booked. But basically the first event should take precedence.

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

    Default Re: Date data validation formula

    If I have a "Green room" event booked for 14/11/2019 it still lets me book a "full venue" event for 15/11/2019.
    Make sure that all the text/terms are the same, it's case sensitive, and check maybe there are extra/trailing space .
    In the example you gave me the term is "Whole venue" not "full venue".

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

    Default Re: Date data validation formula

    Sorry, you're right, it's definitely whole venue not full venue! So if a part venue is booked it shouldn't let the whole venue be booked at all with the 7 days, right? Because there's a few other part venues too, not just the examples I gave. Do the specific ones need to be listed somewhere, or is it set so *any* of the part ones are not compatible with "Whole venue' being booked? (Which is correct).

  6. #16
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Another question, is there a way (in a separate sheet) if I had the Months of the year laid out (sort of calendar style) to have it capture the dates entered for that particular month, but in date order? So at a glance if a manager or somebody wanted to see which dates were booked for "November" they can just look at that and see which weeks have bookings? Ideally I don't want them to need to run a macro or play with a pivot table, just have something that auto-populates from the data.

  7. #17
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,061
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    Sorry, you're right, it's definitely whole venue not full venue!
    Nope, it's "Whole venue", remember it's case sentitive.

    Do the specific ones need to be listed somewhere
    No need.

  8. #18
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,061
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Date data validation formula

    I think you may want to put data validation in col B (listing all the options) to make sure that the user use the exact same terms.

  9. #19
    New Member
    Join Date
    Sep 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    I think you may want to put data validation in col B (listing all the options) to make sure that the user use the exact same terms.
    Thanks

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

    Default Re: Date data validation formula

    Quote Originally Posted by Akuini View Post
    I think you may want to put data validation in col B (listing all the options) to make sure that the user use the exact same terms.
    Ok I'm still having the problem


    If I enter: 1/10/19 Green Room


    it then allows me to enter


    1/10/19 on the next row as well with no warning, and I can put Whole venue.


    But if the first one is Whole venue it won't let me enter the same date again.



    All I've changed in the code is instead of A1:A10 my range is B1:B99 because I have a new column A (for a different purpose) so it's all moved along one. And because we will need more entries than 10 obviously. But that shouldn't affect anything else? Not sure what I've done!

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
  •