Date data validation formula

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
Office Version
365
Platform
Windows
Ah, you're right. Actually you need to fill in the venue first and then the date.
I amended the code so it will force the user to to fill in the venue first.

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?
That's fine.

Try this one:
Code:
[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

    
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"A1:A20"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    
    [COLOR=Royalblue]If[/COLOR] Target.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        MsgBox [COLOR=brown]"You need to pick a venue first"[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        Target.ClearContents
        Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
        [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

        [COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range, flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR], tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
        [COLOR=Royalblue]If[/COLOR] IsDate(Target) [COLOR=Royalblue]Then[/COLOR]
            flag = [COLOR=Royalblue]True[/COLOR]: tx = [COLOR=brown]""[/COLOR]
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"A1:A20"[/COLOR])
                 
                [COLOR=Royalblue]If[/COLOR] Len(c) > [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]And[/COLOR] IsDate(c) [COLOR=Royalblue]And[/COLOR] c.Address <> Target.Address _
                [COLOR=Royalblue]And[/COLOR] (c.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"Whole venue"[/COLOR] [COLOR=Royalblue]Or[/COLOR] Target.Offset(, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"Whole venue"[/COLOR] [COLOR=Royalblue]Or[/COLOR] _
                c.Offset(, [COLOR=crimson]1[/COLOR]) = Target.Offset(, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
                    [COLOR=Royalblue]If[/COLOR] Abs(DateDiff([COLOR=brown]"d"[/COLOR], [COLOR=Royalblue]CDate[/COLOR](Target), [COLOR=Royalblue]CDate[/COLOR](c))) < [COLOR=crimson]7[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    tx = tx & c & [COLOR=brown]" : "[/COLOR] & c.Offset(, [COLOR=crimson]1[/COLOR]) & vbLf
                    flag = [COLOR=Royalblue]False[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
            
            [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]False[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    MsgBox [COLOR=brown]"Pick another date."[/COLOR] & vbLf & [COLOR=brown]"This has been allocated:"[/COLOR] & vbLf & tx, vbCritical
                    Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
                    Target.Activate
                    Target.ClearContents
                    Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
                    [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Ceallach

New Member
Joined
Sep 14, 2019
Messages
20
Ah that's better! Thank you. :)


Any chance you can help with this too?

"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."

So it might look like a row of months with the dates listed underneath.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
Office Version
365
Platform
Windows
Any chance you can help with this too?
Since it's a different problem then you might want to start a new thread for that.
 

Ceallach

New Member
Joined
Sep 14, 2019
Messages
20
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.


I put data validation so now they have to pick from a dropdown menu, it will definitely help. However in the process we’ve realized there’s another criteria that we need to factor in. I can’t say what it is as it’s too identifiable. But let’s say it’s “X” or “Y” or “Z”.

So my question is, can we have a second criteria to compare to? Or does that need to be done separately? The criteria is in the next column along.

So Whole Venue X does not conflict with Whole Venue Y or Whole Venue Z (e.g. they can be booked within 7 days of each other).

Whole Venue Y does not conflict with Green Room X because it’s a whole different criteria (e.g. they can be booked within 7 days of each other).

However, Whole Venue Y conflicts with Green Room Y or Blue Room Y.

Does this makes sense or is this beyond what it can do?


DateVenueCriteria
1-NovWhole VenueX
10-DecGreen RoomY
4-NovWhole VenueY
16-NovBlue RoomZ
30-NovGreen RoomX
20-DecWhole VenueZ

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
Office Version
365
Platform
Windows
Is the second criteria required or it can be empty?
 

Ceallach

New Member
Joined
Sep 14, 2019
Messages
20
Is the second criteria required or it can be empty?
Required. I thought maybe we just specify it within the venue list but that would triple the length of the list, plus means the whole venue conflict would need changing too as there would be multiple conflicts
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
Office Version
365
Platform
Windows
What about green room X with green room Y? is it a conflict?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
Office Version
365
Platform
Windows
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?
 
Last edited:

Forum statistics

Threads
1,089,598
Messages
5,409,196
Members
403,256
Latest member
Viq

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top