Gegevensvalidatie type Lijst

[willema]

Dag allemaal,

Als ik in een cel kies voor Gegevensvalidatie van het type Lijst en bij de Bron voer ik bvb in: a;b;c;d;e dan is deze validatie hoofdlettergevoelig. A;B;C;D;E zijn dan geen geldige waarden en zo wil ik het ook.

Als ik echter deze waarden in de cellen A1:A5 op een apart werkblad zet en dit bereik een naam geef (bvb. LstVal), vervolgens dat bereik bij de Bron invoer als: =LstVal dan valt het me op dat dit niet hoofdlettergevoelig is.

Hoe los ik dit op ?

Groeten,
Marnik

 

[rebmog]

Het klopt dat bij het gebruik van een bereiknaam de validatie niet meer hoofdlettergevoelig is. Dan zal je gebruik moeten maken van VBA. Bijvoorbeeld als de validatielijst in B1 staat:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "$B$1" Then
            .Value = LCase(.Value)
        End If
    End With
End Sub

 

[willema]

Hey Rebmog,

die a;b;c;d;e was maar een voorbeeldje om de vraag wat overzichtelijk te maken.
In praktijk gaat het om een bereik van 1 kolom met 250 rijen, waarin van alles kan staan. Waarden in hoofdletters, zonder hoofdletters, gedeeltelijk hoofdletters, ...

Als ik je VBA-code goed begrijp dan betekent dit dat eender welke ingevoerde waarde wordt omgezet naar kleine letters en dat is niet de bedoeling.

Als bvb. in het bereik zou staan "Senior Member rebmog". Dan zou alleen die schrijfwijze inclusief alle hoofd- en kleine letters als correct mogen gevalideerd worden.

Ik heb wel een ideetje (met VBA), maar weet niet of dit de juiste of snelste manier is:
Stel dat ik VBA het bereik overloop en één lange string maak van alle cellen na elkaar gescheiden door puntkomma.
En vervolgens die string plaats in de bron van alle cellen met gegevensvalidatie bij bvb. het openen/activeren van het werkblad met de gegevensvalidatie.
Zou dit een optie kunnen zijn ? Of kan het beter ?


Groetjes,
Marnik

PS.: Ik bedenk me net dat laatste oplossing geen optie is, want die String gaat snel boven de 255 karakters lang zijn. En is dat niet het maximum ?

 

[rebmog]

Mijn VBA-voorbeeldje was maar een eerste kleine suggestie, niet wetend hoe je bestand is opgebouwd. Als je met VBA de lijst voor gegevensvalidatie wilt vullen, dan loop je inderdaad al gauw tegen de beperking aan van de maximale stringlengte van deze lijst. Misschien dat onderstaande aanpak je wat verder kan helpen:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim varInput As Variant
    With Target
        If .Address = "$B$1" Then
            varInput = Application.Index([LstVal], Application.Match(.Value, [LstVal], 0))
            If StrComp(.Value, varInput, vbBinaryCompare) <> 0 Then
                .Value = varInput
            End If
        End If
    End With
End Sub

 

[willema]

Dankjewel Rebmog,

deze zal ik even moeten bestuderen want er staat nogal wat code in waar ik nog nooit mee gewerkt heb zoals Application.Index en Application Match.
Verder neem ik aan dat de lijstgegevensvalidatie in de cel moet blijven staan ?

Groeten,
Marnik

 

[rebmog]

Ja, de gegevensvalidatie op basis van de lijst moet in de cel blijven staan. Dit dient voor de eerste controle. Als de invoer in de lijst staat, dan wordt met VBA met StrComp een exacte vergelijking gemaakt tussen de invoer en de waarde uit de lijst. De gegevens uit de lijst worden opgezocht met de werkbladformules INDEX en VERGELIJKEN (Application.Index en Application.Match). Als de invoer niet geheel overeenkomt, dan wordt de waarde uit de lijst in de cel geplaatst.

 

[willema]

Dankjewel rebmog voor de deskundige uitleg. Ik probeer het morgen uit en breng dan hier verslag uit!

 

[snb]

De validatielijst kan wel langer zijn dan 255 tekens; hij wordt bij het opslaan van het bestand echter niet bewaard en geeft een foumelding bij hernieusd openen van het bestand.
Als je de lijst bij de opening van het bestand creëeert en bij sluiten van het bestand verwijdert is er weinig aan de hand.

Je kunt ook van de methode Instr gebruik maken, die is als default hoofdlettergevoelig.

Private Sub Worksheet_Change(ByVal Target As Range)
  application.enableevents=false

  If target.Address & "_" & InStr("Abcde", target.value)= "$B$1_0" then target=""

  application.enableevents=true
End Sub

 

[willema]

Ach zo zit dat.
Ik dacht dat het Gegevenstype String nooit langer dan 255 tekens kon zijn.
Dus:

Dim a As String
[COLOR="#008000"]'waarbij a dus nooit langer dan 255 tekens mag zijn
'en zo'n variable wordt toch nooit opgeslagen niet ?

[/COLOR]

Groeten,
Marnik

 

[rebmog]

@snb
Even een paar opmerkingen:
-Dat de validatielijst wel langer kan zijn dat wist ik niet. Dit kan alleen wel problemen geven bij tussentijds opslaan. Is wellicht ook wel weer een mouw aan te passen.
-InStr en ook StrComp zijn niet default hoofdlettergevoelig. Dit afhankelijk van het Option Compare statement. Als dit niet is ingesteld dan is dit standaard Binary, maar als iemand Option Compare Text heeft ingesteld gaat de hoofdlettergevoeligheid verloren. Daarom vind ik het beter om altijd een waarde mee te geven aan het compare-argument.
-Volgens mij is het beter om bij het wijzigen van het werkblad eerst te checken of B1 is gewijzigd en daarna pas de tekstvergelijking te doen (dus geneste If), omdat anders bij elke wijziging van het het werkblad de tekstvergelijking plaatsvindt.

 

[rebmog]

@willema
Een string kan wel zo'n 2 miljard tekens bevatten, dus dat is niet het probleem. Het gaat erom dat de lengte van de validatielijst beperkt is.

 

[snb]

@Rebmog

Jouw tweede bewering bevestigt mijn bewering (tenzij een heel andere betekenis aan 'default' geeft).
De default is binair. Dat geldt voor option compare ook. Pas als je option compare of het argument van instr in een andere 'stand' (c.q. niet default ) zet is de vergelijking niet-binair.

 

[rebmog]

Je kunt natuurlijk twisten wat je onder default waarde kunt verstaan, maar in het DevCenter staat bij InStr beschreven "If compare is omitted, the Option Compare setting determines the type of comparison." Dus voor mij is de default waarde van het compare-argument de instelling van Option Compare en dat is dus niet per definitie Binary. Dit geldt zowel voor InStr als voor StrComp.

Maar daar ging het mij niet om. Het punt dat ik wilde maken is dat je beter altijd het compare-argument een waarde kunt meegeven, zodat deze in alle situaties doet wat je beoogt.

 

[snb]

Citaat:

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.

 

[rebmog]

Dit is mij bekend. Het gaat mij dus om het woordje If in dit citaat. Als Option Compare wél is ingesteld dan wijzigt daarmee de 'default' waarde van het compare-argument van InStr.

Maar laten we er maar over ophouden, want we worden het blijkbaar toch niet eens...

 

[willema]

Hey rebmog,

nu pas kunnen testen en het werkt (bijna) steeds perfect.
Jouw procedure gaat op zoek naar de eerste match in de lijst en neemt die waarde uit de lijst identiek (qua hoofdletters of kleine letters over).
Eén klein probleempje: Stel dat in de lijst opeenvolgend "jan" en "JAN" staat, dan wordt bij een correcte invoer "JAN" toch "jan" in de cel gezet. Omdat dit eerder in de lijst staat.
Is dit te verhelpen ?

 

[willema]

Ik heb een workaround gevonden, maar weet niet of dit de beste optie is.

Ik heb nu twee variabelen (Variant).
De ene:
= Application.Index([LstVal], Application.Match(.Value, [LstVal], 0))
en de andere:
= Application.Index([LstVal], Application.Match(.Value, [LstVal], 1))

De ene zoekt de eerste in de lijst. De andere de laatste.
Vervolgens vergelijk ik beide en als ze niet gelijk zijn komt er een MsgBox (vbYesNo) met de vraag om te kiezen.

 

[rebmog]

Ik zat te denken aan het volgende:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLstVal As String
    With Target
        If .Address = "$B$1" Then
            sLstVal = "|" & Join(Application.Transpose([lstval]), "|") & "|"
            If InStr(1, sLstVal, "|" & .Value & "|", vbBinaryCompare) = 0 Then
                .Value = Application.Index([lstval], Application.Match(.Value, [lstval], 0))
            End If
        End If
    End With
End Sub

Bij invoer van jan en JAN blijft de invoer staan. Maar als nu bijvoorbeeld JaN wordt ingegeven, dan wordt standaard de eerste match weergegeven.

 

[sylvester-ponte]

Marnik, kun je ook een voorbeeld bestandje plaatsen?

 

[willema]

Dankjewel Rebmog,

ook deze zal ik even moeten bestuderen wegens helemaal nieuw voor mij, bvb. die verticale strepen tussen aanhalingstekens ("|").

Hey Sylvester-ponte,

het bestand tot nu toe is nogal complex en bevat vertrouwelijke informatie. Als ik er niet verder uit kom, dan post ik hier een gedownsizede versie met fictieve gegevens.