Gegevensvalidatie filteren/minimaliseren door keuze uit voorgaande gegevensvalidatie

Thijssssss · 14 mei 2018

Beste Excel experts,

Ik heb een vraag waar ik na lang zoeken nog geen antwoord op heb kunnen krijgen. Vandaar mij post op dit forum met de hoop hier iemand te kunnen vinden die mij verder kan helpen.

Ik heb een lijst met elementen waarbij ik in de regel een ruimte locatie wil toevoegen. Deze ruimtes komen uit een andere lijst (de dataset) waarin alle ruimtes zijn opgesomd met daarvoor de etage en een gebouwnummer.
Nu had ik in eerste instantie gewoon gegevensvalidatie toegepast waarbij ik kon kiezen uit de lijst met ruimtes. Hier werden dan automatisch het juiste gebouw en de juiste etage bij gezocht. Nu is het probleem dat de lijst met ruimtes kan oplopen tot ca. 3000 st. waardoor zo'n dropdown-menu niet functioneel/onwerkbaar is.

Nu had ik bedacht om de keuze in ruimtes te minimaliseren door eerst het gebouw en de etage te kiezen. Dus wanneer ik kies voor gebouw x in de 1e kolom moet in de gegevensvalidatie/dropdown van de 2e kolom alleen de verdiepingen zichtbaar zijn die bij dat gebouw horen. Weer een stap verder wil ik in de 3e kolom kunnen kiezen uit de ruimtes die op de etage in en het gebouw zitten die ik in de 2 kolommen ervoor heb gekozen.

Ik heb een voorbeeld Excel bijgevoegd.

Ik heb al diverse opties bekeken zoals bijvoorbeeld:
http://www.contextures.com/xlDataVal02.html
Helaas werkt dit niet omdat: de input van ruimtes anders is opgemaakt, het aantal combinaties ontzettend groot kan zijn (bijvoorbeeld 80 x 4 x 40), de input van ruimtes per locaties varieert. Dit zou dus elke keer weer een hoop tijd kosten waardoor het bestand niet echt meer als template te gebruiken is.

Ik hoop dat iemand mij verder kan helpen. Een alternatief op mijn idee is natuurlijk ook welkom!

Alvast bedankt! Groet, Thijs

Timshel · 14 mei 2018

Welkom op het forum.
Kijk hier eens naar

Thijssssss · 16 mei 2018

Beste Timshel,

Dit is precies wat ik nodig had! Ik ben je zeer dankbaar!
Ik zie dat je het in Visual Basic hebt geschreven. Ik heb zelf geen ervaring met Visual Basic dus het is met niet helemaal duidelijk hoe je het hebt gedaan.
Het is me wel gelukt het één en ander aan te passen waardoor ik het heb kunnen implementeren in mijn eigen Excel-format.

Ik loop echter tegen 2 problemen aan.

1. Op het moment dat ik mijn werkblad wil beveiligen geeft hij een foutmelding (Fout 1004 tijdens uitvoering: Door de toepassing of door object gedefinieerde fout) en werken de dropdown menu's niet meer.
Ik krijg de volgende foutopsporing:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
    Intersect(Target.Offset(, 1).Resize(, 2), Range("B:D")).ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Br
    Dim i As Long, x As Long
    Dim Rng As Range
    
    Set Rng = Range("B:D")
    If Intersect(Target, Rng) Is Nothing Or Target.Count > 1 Then Exit Sub
    Br = Sheets("Ruimte overzicht").Cells(1, 2).CurrentRegion
    x = Target.Column - Rng.Column + 1
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(Br)
            Select Case x
                Case 1
                    .Item(Br(i, x)) = 0
                Case 2
                    If Br(i, 1) = Target.Offset(, -1) Then .Item(Br(i, x)) = 0
                Case 3
                    If Br(i, 1) = Target.Offset(, -2) And Br(i, 2) = Target.Offset(, -1) Then .Item(Br(i, x)) = 0
            End Select
        Next
        Target.Validation.Delete
        If .Count > 0 Then [COLOR="#FF0000"]Target.Validation.Add xlValidateList, , , Join(.Keys, ",")[/COLOR]
    End With
End Sub

Het zit hem in de regel: Target.Validation.Add xlValidateList, , , Join(.Keys, ",")
Ik verwacht dat hij de gegevensvalidatie niet automatisch kan veranderen omdat het blad beveiligd is. Klopt dit? En is hier een oplossing voor?

2. Bij het opnieuw opstarten van mijn Excel-bestand krijg ik een foutherstel (Verwijderde functie: Gegevensvalidatie van het onderdeel /xl/worksheets/sheet3.xml) en zijn alle andere gegevensvalidatievelden die er in het werkblad zaten verwijderd. Is dit te voorkomen?

Groet, Thijs

snb · 16 mei 2018

Gebruik Code tags:

Zie hier: http://www.helpmij.nl/forum/announcement.php?f=5

Timshel · 16 mei 2018

Wat snb zegt. Als je code tussen tags plaatst ziet het er een stuk netter uit.

1. Het probleem waar jij tegen aanloopt is nieuw voor mij. Het doet zich ook voor als cellen op een vergrendeld werkblad unlocked zijn. Het werkblad programmatisch vergrendelen met de switch UserInterfaceOnly:=True helpt ook niet afdoende. Dan rest alleen nog onderstaande oplossing die mogelijk trager werkt.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:F")) Is Nothing Then Exit Sub
    Intersect(Target.Offset(, 1).Resize(, 2), Range("E:G")).ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Br
    Dim i As Long, x As Long
    Dim Rng As Range
    
    Set Rng = Range("E:G")
    If Intersect(Target, Rng) Is Nothing Or Target.Count > 1 Then Exit Sub
    Br = Sheets("Dataset").Cells(1, 2).CurrentRegion
    x = Target.Column - Rng.Column + 1
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(Br)
            Select Case x
                Case 1
                    .Item(Br(i, x)) = 0
                Case 2
                    If Br(i, 1) = Target.Offset(, -1) Then .Item(Br(i, x)) = 0
                Case 3
                    If Br(i, 1) = Target.Offset(, -2) And Br(i, 2) = Target.Offset(, -1) Then .Item(Br(i, x)) = 0
            End Select
        Next
        Target.Validation.Delete
        If .Count > 0 Then
            ActiveSheet.Unprotect Password:="Rotsformatie"
            Target.Validation.Add xlValidateList, , , Join(.Keys, ",")
            ActiveSheet.Protect Password:="Rotsformatie"
        End If
    End With
End Sub

2. Deze code in de ThisWorkbook-module

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("Werkblad").Range("E:G").Validation.Delete
End Sub

snb · 16 mei 2018

zie de bijlage

Thijssssss · 17 mei 2018

Mijn excuses. Heb hem tussen tags geplaats. Ziet er inderdaad een stuk netter uit!

@Timshel: Bedankt voor de hulp! Hij doet nu precies wat ik wil. Hij moet na een keuze uit de dropdown inderdaad even laden omdat het wachtwoord er continu af en weer terug op gezet moet worden. Zolang het gaat zoals nu is dit prima en niet hinderlijk. Het moet nog even blijken of dit erger wordt wanneer het blad verder gevuld is en er nog enkele extra formules in komen te staan. Dit ga ik nog even testen.

@snb: Wanneer je een keuze maakt in de eerste kolom geeft hij een Compileerfout: "End if zonder blok if". Ik heb de "End if" vervangen voor "End select" en dan doet hij het. Alleen wanneer één keer een object cel wordt verwijderd wanneer etage en ruimte nog zijn ingevuld gaat hij nieuwe foutmeldingen geven. Deze zijn vervolgens ook niet meer weg te krijgen. Voor nu werkt de oplossing van Timshel goed. Mocht uiteindelijk blijken dat het anders moet dan ga ik me verder in deze optie verdiepen.

In ieder geval bedankt voor de hulp allemaal!

snb · 17 mei 2018

Ik had blijkbaar de verkeerde versie geplaatst:

Gegevensvalidatie filteren/minimaliseren door keuze uit voorgaande gegevensvalidatie

Thijssssss

Nieuwe gebruiker

Bijlagen

Timshel

Terugkerende gebruiker

Bijlagen

Thijssssss

Nieuwe gebruiker

snb

Verenigingslid

Timshel

Terugkerende gebruiker

snb

Verenigingslid

Bijlagen

Thijssssss

Nieuwe gebruiker

snb

Verenigingslid

Bijlagen

Nieuwste berichten

Wij waarderen jouw privacy