"Kalender" waarbij de input blijft staan bij het wisselen van de maand

[Brockenspook]

Beste Excelfanaten,

Momenteel ben ik bezig met een soort kalender. Nu wil ik echter niet dat ik eerst helemaal naar de maand december in bijvoorbeeld 2018 moet scrollen voordat ik mijn data kan invoeren. In plaats daarvan wil ik dat ik de maand kan aanklikken om vervolgens direct de desbetreffende maand in beeld te krijgen, zoals te zien is in onderstaande Excelsheet. Het probleem is echter dat wanneer ik nu bijvoorbeeld in december 2018 iets invoer en ik naar januari 2019 ga de input blijft staan. Wat ik zou willen is dat de invoer dan vervolgens op de achtergrond blijft en ik opnieuw in dezelfde cel iets zou kunnen invoeren. Ik zat te denken aan een macro waarbij de informatie wordt geplaatst in een output blad, vervolgens zal als ik weer terug naar december 2018 zou gaan de informatie weer staan op de plek waar ik deze in eerste instantie had ingevuld. Echter heb ik zelf verreweg niet genoeg kennis van macro's om te weten of dit mogelijk is en als dit mogelijk is hoe. Ik ben benieuwd naar de mogelijke oplossingen! Mocht je nog vragen hebben dan hoor ik deze graag.

Met vriendelijke groet,
Brockenspook

 

[edmoor]

Uiteraard is dat mogelijk, maar waarom zou je het wiel opnieuw willen uitvinden?
Gebruik 1 van de vele sjablonen die al bestaan voor een agenda in Excel:


https://templates.office.com/en-us/Calendars

 

[Brockenspook]

Het probleem van deze templates is echter dat wanneer je van maand wisselt de gegevens die je hebt ingetypt gewoon blijven staan. Dit is dus precies wat ik niet wil. Daarnaast voldoet geen van deze kalenders aan mijn opzet (zie het linker gedeelte van de excel-sheet die als bijlage is toegevoegd).

 

[VenA]

Wat jij wil kan alleen met een beetje code. Je zal de data op moeten slaan om deze ook weer op te kunnen halen. De formules kunnen ook wel wat eenvoudiger en waren ook niet correct. 1-1-2019 valt op een dinsdag en niet op een woensdag.

 

[Brockenspook]

Super, dit is precies wat ik wil! Zojuist de VB bekeken en dat ziet er uit als wat meer dan 'een beetje code' erg indrukwekkend! Nog een vraagje: Als 2019 aanbreekt en ik zou 2023 er dus in gaan zetten (zodat er weer 5 jaar vooruit kan worden gekeken), is er dan een manier waarop ik ook bij het output blad deze informatie daar in krijg? Door bijvoorbeeld het bereik van de VB aan te passen en dan simpelweg de nieuwe data onder het output blad te zetten? Of als ik er bijvoorbeeld een extra kolom (kolom L) aan wil plakken.

 

[VenA]

Op het output blad zal je de nieuwe datums eerst moeten toevoegen. De code voegt geen nieuwe datums toe. Als je extra kolommen wil gebruiken dan moet je de code even aanpassen. De rood gemarkeerde stukjes zijn van invloed op het aantal kolommen.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range([COLOR="#FF0000"]"C3:J33[/COLOR], P2,R2")) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  With Sheets("Output blad").ListObjects(1).DataBodyRange
    If Target.Row = 2 Then
      Range("[COLOR="#FF0000"]C3:J33[/COLOR]").ClearContents
      Cells(3, 3).Resize(Application.EDate([B3].Value, 1) - [B3], [COLOR="#FF0000"]8[/COLOR]) = .Columns(1).Find([B3].Value, , xlValues, xlWhole).Offset(, 1).Resize(Application.EDate([B3].Value, 1) - [B3], [COLOR="#FF0000"]8[/COLOR]).Value
     Else
      .Columns(1).Find(Cells(Target.Row, 2).Value, , xlValues, xlWhole).Offset(, Target.Column - 1) = Target.Value
    End If
  End With
    Application.EnableEvents = True
End Sub

 

[Brockenspook]

Super, dat werkt perfect! Echter heb ik wel een foutje gevonden, op het moment dat ik bijvoorbeeld 5 invul in de CEL E12 en het Snijpunt 10-1-2018 en C in de tabel dan zie ik dat deze als snijpunt bij het output blad op 10-1-2018 en D staat in de tabel. Als ik vervolgens van maand wissel dan is deze 5 ook opgeschoven naar het snijpunt 10-1-2018 D in de tabel. Het is waarschijnlijk maar ergens binnen de formule een kleine aanpassing, maar kan hem zelf niet vinden. Heb jij enig idee?

 

[VenA]

Het is geen formule.
Probeer het zo eens.

.Columns(1).Find(Cells(Target.Row, 2).Value, , xlValues, xlWhole).Offset(, Target.Column [COLOR="#FF0000"]- 2[/COLOR]) = Target.Value

 

[Brockenspook]

Owwja, VBA-code natuurlijk. Geweldig, dat is hem. Hartstikke bedankt voor al je hulp!

 

[Brockenspook]

Wanneer ik de positie van de maanden en de jaren wil veranderen naar een andere kolom op dezelfde rij (rij 2). Dan kan ik dit simpelweg doen door het rode gedeelte in de code te veranderen zoals hieronder te zien:

 If Intersect(Target, Range("C3:V33, [COLOR="#FF0000"]P2,R2[/COLOR]")) Is Nothing Then Exit Sub

Maar wat ik bij nader inzien zou willen is dat ik de maanden en jaren respectievelijk op D1 en F1 wil hebben. Echter als ik nu dezelfde aanpassing doe als hierboven en ik de maand of het jaar aanpas krijg ik de foutmelding: Fout 91 tijdens uitvoering: Objectvariabele of blokvariabele With is niet ingesteld. Nu geeft de foutmelding aan dat de fout zich in de onderstaande rode rij van code bevind:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C3:V33, B1,D1")) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  With Sheets("Output blad").ListObjects(1).DataBodyRange
    If Target.Row = 2 Then
      Range("C3:V33").ClearContents
      Cells(3, 3).Resize(Application.EDate([B3].Value, 1) - [B3], 20) = .Columns(1).Find([B3].Value, , xlValues, xlWhole).Offset(, 1).Resize(Application.EDate([B3].Value, 1) - [B3], 20).Value
     Else
[COLOR="#FF0000"]      .Columns(1).Find(Cells(Target.Row, 2).Value, , xlValues, xlWhole).Offset(, Target.Column - 2) = Target.Value[/COLOR]
    End If
  End With
    Application.EnableEvents = True
End Sub

Hoe zou ik de code moeten aanpassen om wel tot het gewenste resultaat te komen?

 

[HSV]

Dat gaat niet vanaf cel B1.
Target = B1
B1.offset(,-2) = foutmelding.
Er zijn aan de linkerkant van kolom B geen twee kolommen.

 

[Brockenspook]

Bedankt, als ik de maanden nu aanpas geeft deze inderdaad niet direct de foutmelding. Echter als ik in C3 een getal invoer dan krijg ik dezelfde foutmelding. Welke aanpassingen moet ik verder nog maken?

 

[VenA]

Plaats even een nieuw bestand met alle aanpassingen die je zoal gedaan hebt.

 

[Brockenspook]

Bij deze het bestand met de aanpassingen die ik gedaan heb. Ben benieuwd of jullie er iets van kunnen maken.

 

[VenA]

Wat moet er gebeuren dan? Wat heb je gewijzigd? Of moeten we eerst onderzoeken wat lang geleden jouw initiële vraag was en dan maar weer iets panklaar aanleveren?

 

[Brockenspook]

De vraag is in principe nog hetzelfde maar het bereik en de positie van sommige cellen is veranderd. De soll-situatie ziet er als volgt uit: Ik zou graag een bestand willen waarbij de desbetreffende maand en het desbetreffende jaar kan selecteren middels gegevensvalidatie. Om vervolgens op desbetreffende maand en het desbetreffende jaar gegevens in te vullen. Wanneer ik van maand wissel wil ik dat de gegevens wel op het output blad te zien zijn maar niet meer op het input blad. Deze gegevens moet ik dan wel weer kunnen zien op het input blad wanneer ik terug ga naar dezelfde maand en jaar.

Bij deze de aanpassingen op een rijtje:
- Het inputgebied is vergroot van C3:J33 tot C3:V33
- De maand en het jaar zijn verplaatst van P2 en R2 naar E1 en G1.

Onderstaand is de laatst geprobeerde code te zien:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C3:V33, E1,G1")) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  With Sheets("OUTPUT").ListObjects(1).DataBodyRange
    If Target.Row = 2 Then
      Range("C3:V33").ClearContents
      Cells(3, 3).Resize(Application.EDate([B3].Value, 1) - [B3], 20) = .Columns(1).Find([B3].Value, , xlValues, xlWhole).Offset(, 1).Resize(Application.EDate([B3].Value, 1) - [B3], 20).Value
     Else
      .Columns(1).Find(Cells(Target.Row, 2).Value, , xlValues, xlWhole).Offset(, Target.Column - 2) = Target.Value
    End If
  End With
    Application.EnableEvents = True
End Sub

Excuses voor de beperkte info en de late reactie. Dit zal ik in de toekomst niet meer laten gebeuren. Ik waardeer oprecht alle hulp die ik krijg en het was zeker niet de bedoeling om jou/jullie een groot gedeelte van het werk te laten doen.

 

[VenA]

Een beetje verdiepen in wat Row, Column en Offset is zal niet misstaan.

If Target.Row = [COLOR="#FF0000"]1 [/COLOR]Then

 

[Brockenspook]

Bedankt, dat ga ik zeker doen.