Afhankelijke validatie n.a.v. @snb

[HSV]

N.a.v. de afhankelijke validaties uit een draaitabel van @snb (zie link hieronder).

https://www.helpmij.nl/forum/showthread.php/940852-Afhankelijke-(getrapte)-validatie-met-een-draaitabel

In de bijlage een geheel andere methode:
Ik laad de validaties eenmaal in met 'Workbooks_open() met de Dictionary methode.
Bij verandering worden de twee anderen op basis van elkaar gevuld c.q. veranderd van waarden op basis van de Workbook_open() code.

Graag jullie bevindingen.

Alvast bedankt voor het kijken naar de bijlage of enige reactie.

 

[Mde]

Hallo HSV,

Bij het openen krijg ik volgende melding in excel 2016



en bij de 3e gegevensvalidatie, in kolom G, krijg ik foutmelding



voor rest werkt het.

 

[SjonR]

Harry,


dit is de log na het bestand te hebben hersteld:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error032320_01.xml</logFileName><summary>Er zijn fouten aangetroffen in bestand C:\test\__getrapte validatie_hsv.xlsb</summary><additionalInfo><info>Validatie en herstel zijn uitgevoerd op bestandsniveau. Bepaalde onderdelen van deze werkmap zijn mogelijk hersteld of verwijderd.</info></additionalInfo><repairedRecords><repairedRecord>Herstelde records: Formule van het onderdeel /xl/worksheets/sheet1.bin</repairedRecord></repairedRecords></recoveryLog>

 

[HSV]

Bedankt voor de reacties Marco en Sjon, wordt zeer gewaardeerd.

Waarom het in andere versies dan Excel 2007 niet lekker gaat zou ik zo niet weten (ik plaats hier veel bestanden zonder klachten).
Het was misschien ook niet zo handig om op iemands anders bestand voort te borduren.

In de bijlage een nieuw opgezet bestand.
Ik hoop dat deze beter werkt.

 

[snb]

Het probleem heeft niet met versies te maken, maar met de beperkingen van Excel.
Validation kan slechts een zeer beperkt aantal gegevens bevatten.
Daarvan heb je geen last zo lang het bestand geopend blijft.
Als het bestand wordt opgeslagen wordt een deel van de gegevens in de validatieregel verwijderd.
Als je het bestand opnieuw opent krijg je een melding dat er gegevens verloren zijn gegaan.
Het vullen van de validatieregel zal opnieuw moeten plaatsvinden

Alleen als je werkt met een verwijzing naar een opslagplaats elders (in een werkblad) gaat het bij omvangrijkere gegevens soepel.

 

[SjonR]

exact dezelfde foutmelding, maar gezien de verklaring van snb ook niet gek.

 

[HSV]

Dat is vreemd en mij niet bekend.
Ik sluit het bestand en heropen het, ook al is de pc afgesloten geweest, en het werkt gewoon.

Het vullen van de validatielijsten gebeurt bij het open van het bestand in Worbook_open() met gegevens uit het werkblad, dus dat zal het probleem niet kunnen zijn.

Ik ben benieuwd naar reacties van gebruikers van Excel 2007.

 

[snb]

De melding geeft het al aan:

Herstelde records: Formule van het onderdeel /xl/worksheets/sheet1.bin

De enige formule in het werkblad is die van de validationregel.

 

[HSV]

Bedankt, dat brengt me misschien wel tot iets.

Ik zal het vanavond als ik er aan toe kom eens proberen aan te passen om de validaties te deleten en de gegevens te behouden (zover ben ik al), maar om ze op elk niveau terug te plaatsen in de twee laatste kolommen.

Dat gebeurt nu nog door de change_event.

Bedankt voor je reactie.
Wordt vervolgd.

 

[snb]

@HSV

Ik vind de Dictionary in een Dictionary nogal vermetel in je aanpak :thumb:

Ik zou de omvang van het listobject beperken tot het gebied waarin keuzes kunnen worden gemaakt.

Ook zou ik een pleit willen dooien voor het gebruik van Modify bij Validation.

Bij het afsluiten van het werkboek verwijder ik de inhoud van de valdatielijsten, zodat het hierboven geschetste probleem niet optreedt.

Het heeft mijn voorkeur de reikwijdte van de dictionary te beperken tot het werkblad waarin die voorkomt: blad 1
De Dict variabele is daardoor 'private' en niet 'public'.
De Selection_change gebeurtenis is daarvoor voldoende en m.i. net iets robuuster.

Zie Bijlage 1

In het bestand waarin ik de draaitabel introduceerde had ik ook een Dictionaryvariant opgenomen met een iets andere techniek (geen Dictionary in Dictionary)

zie Bijlage 2

 

[HSV]

@snb,

Mooi dat je het duimpje hebt geplaatst, want vermetel heeft diverse betekenissen.

Ik je bestand doorgenomen en hoefde daarbij niet echt m'n hoofd breken voor mijn opzet, waarvoor dank. :thumb:
Even de validation op een lege range zetten tijdens het afsluiten vind ik wel weer doordacht (met de before_close methode was ik al zover en het hiervoor benoemde paste daar mooi in).

Ik ben niet zo van de selection_change om de dictionary telkens te laten lopen en de validatie te wijzigen, maar dat maakt niet uit (het is mijn event niet, maar toegegeven wel gemakkelijk).
Waar jij een validation delete en add doet bij het afsluiten doe ik nu een modify doordat het bereik nu standaard op een leeg bereik staat ("=$K$1:$K$2").
Het bestand is ook zodanig opgeslagen.

Het voordeel van de opzet in het Workbook_open() event is dat je direct kunt schakelen in de validaties in kolom 5 en 6 bij het openen van het bestand, i.p.v. opnieuw te beginnen in kolom 4 omdat de naastgelegen validaties op dat moment nog leeg zijn.
De modify wordt toegepast d.m.v. de change_event.

Ik hoop dat het zo wel werkt bij de mensen bij wie het in de voorgaande bestanden niet werkte.
Voor verbeteringen ben ik ook benieuwd en zie ik graag tegemoet.

Als het nog niet werkt dan kunnen we altijd het bestand van @snb gebruiken.
Ik ben benieuwd.

Ps. Dit is zomaar een experiment om wat te goochelen met de dictionary.

 

[snb]

@HSV

Vermetel heeft hier in de omgeving alleen maar positieve connotaties.....(gewaagd, gedurfd, maar niet roekeloos)

 

[Mde]

@ HSV

De foutmelding bij openen is weg :thumb:
De 2e foutmelding nog niet helemaal, als ik bij de 2e validatie het eerste kies dan geen fout melding, maar kies ik alles anders dan komt bij de 3e validatie weer de 2e foutmeding uit post #2

voor de rest heb ik er niet zoveel verstand van.

 

[VenA]

Dic.Item(sv(i, 1)) = Array(sv(i, 1), CreateObject("scripting.dictionary"))

Hoe verzin je het? Werkt prima in XL-2007 en XL-2016:thumb:

Kleine aanvulling:

Case 5
  Target.Offset(, 1).ClearContents

@Mde, Het is geen foutmelding, maar een melding over niet consistente data. Dit kan je in de opties uitzetten.

 

[HSV]

Bedankt voor de reacties, ik kon de foutmelding ook al niet reproduceren.

Mooi dat het het nu ook werkt in Excel 2016.
Wat betreft de aanvulling @VenA; die had ik in mijn laatst geplaatst bestand net aangevuld op 22:25 uur. :thumb:

Ja, hoe kom ik er op?
Ik kwam de dictionary in de dictionary methode vorige week tegen.
Er werd gewerkt met Add en Item en dergelijke; Ik heb het naar mijn eigen hand gezet (ingekort, veranderd, etc.) zonder al die fratsen.

Daarna bedacht ik mij dat het misschien wel om te bouwen was naar drie validatielijste, comboboxen in een Userform zoals mijn onderwerp titel aangeeft.
Dat ging nog niet zo van een leien dakje als je de verschillen ziet in beide codes als je onderstaand bestandje bekijkt.

Vooral de derde validatie was een struikelblok van een middagje stoeien.
Op een gegeven moment zie je niet meer wat wat doet (dan faalt er dit, dan weer dat).
Voor jezelf heb je een idee dat het werkt, maar de reacties waren getuige van niet, tot @snb me een duwtje gaf.

Maar goed, mooi dat het werkt.

 

[VenA]

Mooie oplossingen:thumb: maar geeft wel weer hoofdbrekens.
Over hoofdbrekens en blindstaren gesproken. Het principe van een dictionary begrijp ik ongeveer of toch totaal niet. Ik vind het maar een lastig object om te debuggen. In de bijlage heb ik een poging gedaan (zeer veel pogingen) om een extra 'trap' aan de validatie toe te voegen. Dit werkt bij het vullen van Combobox1 en Combobox2 bij Combobox3 krijg ik de waarden terug die gerelateerd zijn aan Combobox1. Combobox3 zou eigenlijk maar 1 optie moeten hebben maar dat krijg ik niet voor elkaar. Waar ga ik de mist in?

 

[HSV]

Zover ben ik @VenA,

De drie comboboxen worden gevuld; geen dubbele waarden.

Ik moet er zo van door, dus misschien kan jij in de tussentijd de textboxen vullen.

 

[HSV]

Het is inderdaad moeilijk te doorgronden wat wat is, en of doet.
Wat spelen met MsgBoxes en heb een oplossing kunnen creëren om de textboxen ook te vullen.
Of ze nu alleen afhankelijk zijn van alleen combobox 2 en 3 heb ik met deze gegevens nog niet verder getest.

combobox1 op "a"
combobox2 op "y"
combobox3 > keuze "n" of "s".

 

[VenA]

Werkt als een zonnetje :thumb:

 

[HSV]

Bedankt voor de test @VenA.

Leuk opdracht van je.

Ik was toch niet helemaal tevreden met de vorige, daar het resultaat van de textboxen alleen gebaseerd waren op combobox1 en 3 (na wat testjes te hebben gedaan).
Dat gaf de code in principe ook al aan.
Ik denk dat ik met onderstaand bestand een betere oplossing heb die op alle drie waarden van de comboboxen reageert.


De combinaties 'a, v, s', 'a, y, s' en 'b, y, s' laten dat nu duidelijk zien.