Gegevensvalidatie met formule?

[Senso]

Ik zoek iets dat: > Als jaartal in C7 is 2021 of 2022 dan kan C6 niet hoger zijn dan 52 en kan dus niet ingevuld worden.

 

[JEC.]

Bijvoorbeeld zo onder Custom validation.
Voor cel C6:

=NOT(OR(C7=2021;C7=2022))

 

[Senso]

=NIET(OF(C7=2021;C7=2022))

Zo kan ik geen waarden invoeren van 01 tm 52 in de jaren 2021 en 2021

 

[JEC.]

O dus je wilt wel waarden kunnen invoeren als er 2021 of 2022 in staat

 

[Senso]

Ja, dat zijn de weeknummers die geldig zijn in die jaren. 53 mag alleen in 2020 en 52 is max voor 2021 en 2022. Zo wil ik dat voor andere jaren ook regelen dus die formule lijkt mij verder wel mooi.

 

[JEC.]

Regel is: Als de eerste dag of de laatste dag van het jaar op weekdag 4 valt, heeft het jaar 53 weken. Zie rode regel in code

Plaats deze achter je betreffende werkblad

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
  If Not Intersect(Target, Range("C6:C7")) Is Nothing And Target.Count = 1 Then
   [COLOR="#FF0000"]If Weekday(DateSerial([c7], 12, 31), 2) = 4 Or Weekday(DateSerial([c7], 1, 1), 2) = 4 Then[/COLOR]
     If [C6] > 52 Then
      .EnableEvents = False
       MsgBox "Jaar heeft maar 52 weken", vbCritical, "Let op"
      .Undo
      .EnableEvents = True
     End If
   End If
  End If
 End With
End Sub

 

[Senso]

Bedankt voor de inzet. Ik plaats het document wel. Als ik met jouw vba in 2021 01 of 1 in C6 wil invoeren gaat dat niet.
Bekijk bijlage Excel Week - Welke dagen vallen in weeknummer 6.xlsm

 

[JEC.]

Je had de code in de module.
Hij moet achter het betreffende werkblad, zoals in de bijlage. Ook de gegevensvalidatie even verwijderd voor cel C6.

 

[snb]

Sub M_snb()
    Cells(2, 1).Validation.Add 7, , , "<=weeknum(date(year($C$7),12,31),21)"
End Sub

 

[Senso]

@JVeer. Het is toch heel ingewikkeld. Jij zegt met vba dat 2020 52 weken is en ik heb hier twee kalenders en daar staat bij 2020 53 weken. Voer ik bij 2022 week 53 in dan kan dat, wat niet zou moeten kunnen, immers 2022 heeft net als 2021 52 weken. Op internet staat dat ook 2026 zou weer 53 weken hebben.

 

[JEC.]

Ik zie het nu, foutje in de code.
Vervang de code door onderstaand.

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
  If Not Intersect(Target, Range("C6:C7")) Is Nothing And Target.Count = 1 Then
   If Weekday(DateSerial([c7], 12, 31), 2) <> 4 And Weekday(DateSerial([c7], 1, 1), 2) <> 4 Then
     If [C6] > 52 Then
      .EnableEvents = False
       MsgBox "Jaar heeft maar 52 weken", vbCritical, "Let op"
      .Undo
      .EnableEvents = True
     End If
   End If
  End If
 End With
End Sub

 

[Senso]

Bij het testen kan ik nog steeds bij het jaartal 2022 53 weken invoeren. De data lopen dan van 2/1 tm 8/1 en dat is Week 1 in 2023.

 

[JEC.]

Heb je de code wel achter je werkblad gezet ipv een module?

Zie bijlage.
Probeer maar eens de 52 te veranderen in 53 (in C6)

 

[Senso]

Op dat moment is het goed en dat lukt niet. Echter bij het verder testen en ik verwijder weeknummer en jaartal en voer in jaartal 2021 dan kan ik invoeren wk 53 en dat moet niet kunnen in 2021, immers 2021 heeft 52 weken en dan zit je dus in week 1 van 2022.

Ik heb dus nu jouw document gedownload! Wellicht toch een combinatie vba met gegevensvalidatie?

 

[JEC.]

Dat wat jij omschrijft heb ik even getest.
Ik kan toch echt geen week 53 selecteren, ook niet als ik het jaartal leeghaal en opnieuw invoer.

Edit: Voer je soms een datum in in cel C5 om het weeknummer te verkrijgen? Als dat zo is, wordt de macro niet geactiveerd.
Hij gaat alleen lopen wanneer je handmatig iets intikt in C6 of C7

 

[Senso]

Dan heb ik ergens een fout gemaakt. Sorry. Nu had ik het weer getest en toen ging het wel goed. Dus ik ga vanavond nogmaals een testronde doen. Moet vanmiddag nog weg. Bedankt.:thumb:

 

[JeanPaul28]

Een poging met formule en hulprij

 

[Senso]

@JVeer,
Werkt nu vlekkeloos en perfect. Uitvoerig getest. Bedankt.
Omdat jij nu in de materie zit, zou je willen kijken naar dat tweede kader en of je dat in overeenstemming kunt brengen met het eerste kader?
in het eerste staat wk 13 2021. Pak ik 29/03/2021 en vul dat in G5 staat in G6 weeknr 14. Dat kan natuurlijk niet. De weekdagen kloppen dan ook niet. Office Home and Students 2007.

 

[HSV]

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
  If Not Intersect(Target, Range("C6:C7")) Is Nothing And Target.Count = 1 Then
[COLOR=#ff0000]    If [C6] > DatePart("ww", DateSerial([c7], 12, 31), vbMonday, vbFirstFourDays) Then  [/COLOR]'of DatePart("ww", DateSerial([c7], 12, 31), 2,2)[COLOR=#ff0000][/COLOR]
      .EnableEvents = False
       MsgBox "Jaar heeft maar 52 weken", vbCritical, "Let op"
      .Undo
      .EnableEvents = True
     End If
   End If
 End With
End Sub

 

[Senso]

Als ik in G5 01/01/2021 zet dan krijg ik met =JAAR(G5) 2021. Dat is goed.
in G6 met formule:

=GEHEEL(((G5+4-ALS(WEEKDAG(G5)=1;7;WEEKDAG(G5)-1))-DATUM(JAAR(G5+4-ALS(WEEKDAG(G5)=1;7;WEEKDAG(G5)-1));1;1))/7)+1

Er staat dan in G6 week 53, dat is ook goed ware het dat dit week 53 is van 2020. Dus de cijfers maandag t.m. zondag worden 03/01/22 t/m 09/02/22. Dat is natuurlijk niet goed. Het moet 28/12/20 tm 03/01/21 zijn, immers de datum is 01/01/2021.