Foutmelding Validatie met VBA

[willema]

Dag allemaal,

in het Excel-hoofdstuk van dit forum heb ik een methode gevonden om in een validatielijst (dropdown) van een cel enkel de eerste letters te moeten invoeren om zo alleen die waarden te zien om uit te kiezen.
Dat werkte perfect met .xls, maar in eerste instantie niet met .xlsm. Ook dat werd hier opgelost, behalve dan de de Foutmelding van gegevensvalidatie moet worden uitgezet.
Dus kunnen nu alle waarden worden ingetikt zonder te kiezen uit het dropdownmenu.

Ik wou dit ondervangen met een foutafhandeling in VBA als volgt (ook gebaseerd op iets wat ik op dit forum heb gevonden):

Private Sub Worksheet_Change(ByVal Target As Range)

    ChkValST Target

End Sub

en

Public Sub ChkValST(ByVal Target As Excel.Range)
'controleert geldige invoer in kolom 3

    On Error GoTo EndMacro

    'bepalen of target valabel is
    If Application.Intersect(Target, Range(Cells(2, 3), Cells(75, 3))) Is Nothing Then
        Exit Sub
    End If

    If Target.Cells.Count > 1 Then
        Exit Sub
    End If

    If Target.Value = "" Then
        Exit Sub
    End If

    MsgBox "ChkValSt"
    
    If Range("lijst SETS!A2:A250").Find(Target.Value) Is Nothing Then
        MsgBox "ChkValSt NOK"
    Else
        MsgBox "ChkValSt OK"
    End If

EndMacro:

End Sub

Bij testen zet ik er vaak MsgBox-en tussen:
De MsgBox "ChkValST" gebruik ik als test of de code goed doorlopen wordt, maar geen van beide volgende verschijnt, noch "ChkValST NOK" als "ChkValST OK" (en ik verwacht er bij een test toch één van de twee).
lijst SETS!A2:A250 is het (aparte) werkblad en het bereik waarbinnen alle mogelijke validaties staan.

Groeten,
Marnik

 

[HSV]

Het is een bekend probleem wat ik al schreef in je eerdere vraag hier over.
Met onderstaande macro in Thisworkbook is het klusje zo geklaard.

Bv. als de validaties in blad1!A1:A9 staan.

Private Sub Workbook_Open()
 Sheets("Blad1").Range("A1:A9").Validation.Modify xlValidateList
End Sub

 

[willema]

Dankjewel Harry,

deze code lost inderdaad het "blokkeren" van lijstvalidatie bij opstarten van een xlsm-werkmap op. Maar dat was al opgelost (zonder code) in een werkmap op een forumpagina waarnaar je me liet doorlinken in je vorige post.

Maar (heb wat getest) ook nu nog moet de Foutmelding van die validatie uitgeschakeld zijn. Om het even welke invoer, niet uit de lijst, wordt dus geaccepteerd.
Dat wou ik ondervangen met bovenstaande code, die dus geen resultaat geeft (maar ook geen fout).
Ik zoek naarstig verder

Nu zou zich ook een probleem stellen met uw voorgestelde code in mijn definitief resultaat (dat er hopelijk gauw komt):
De validatie staat op een werkblad die ik Basis noem. Het is de bedoeling dat de gebruiker zo'n blad dupliceert (en een eigen naam geeft), en er vervolgens zijn waarden in voert. Dat werkblad heet dan niet langer zoals in jouw code benoemt en hoe de gebruiker het gaat noemen kan ik ook niet inschatten.
Ik vermoed dat ik jouw coderegel dan ga moet zetten in iets dat lijkt op "For Each Sheet in ..." i.p.v. te verwijzen naar Sheets("Basis").Range..." ?

Groetjes,
Marnik

 

[HSV]

Laat alles eens in een bestandje zien zodat we kunnen zien wat je bedoeld.

 

[willema]

Alstublief, Harry

 

[willema]

Zelf opgelost:

If Range("lijst SETS!A2:A250").Find(Target.Value) Is Nothing Then

aangepast naar

If Worksheets("lijst SETS").Range("A2:A250").Find(Target.Value) Is Nothing Then

alhoewel ik eerder bovenstaande notatie voor een bereik op een ander werkblad ook al heb gebruikt.

 

[willema]

'k Ben er nog niet helemaal

If Worksheets("lijst SETS").Range("A2:A250").Find(Target.Value) Is Nothing Then

kijkt alleen of de ingevoerde waarde (Target.Value) voorkomt in het bereik A2:A250 op het werkblad "lijst SETS".
Dus ook als een deeltje van een waarde in dat bereik voorkomt is aan de voorwaarde voldaan.
bvb. als Target.Value = "fo" en er komt in die lijst een woord voor met "fo" zoals "forum", dan is aan de voorwaarde voldaan.
En dat wil ik niet. "fo" is geen juiste waarde, wel de volledige celinhoud ofte het volledige woord "forum" of met hoofdletters "Forum", "FORUM", etc.

Ik vermoed dat ik hiervoor niet de instructie Find moet gebruiken maar VLookUp.

Heb al een aantal testen gedaan, maar stoot telkens op een fout.

Heeft iemand een code die hiervoor bruikbaar is ?

Dank en groeten,
Marnik

 

[willema]

Pffff... helemaal op de verkeerde piste.

Mijn bedoeling was:
1. Je tikt bvb. "aa" in de cel.
2. Klikt op het driehoekje.
3. Een keuzelijst verschijnt met daarin enkel de waarden die beginnen met "aa"
4. Je maakt een keuze uit die lijst.

Met de foutmelding van gegevensvalidatie AAN verschijnt die fout al meteen na punt 1 wegens "aa" geen (volledige) waarde uit de lijst.
Met de foutmelding van gegevensvalidatie UIT is het mogelijk om na punt 1 de cel te verlaten met de verkeerde (onvolledige) waarde "aa" ingevoerd.
Met een probeersel om de foutafhandeling met VBA uit te voeren lukt het ook niet want Worksheet_Change gebeurt ook al vóór punt 3. Dus je hebt niet de mogelijkheid om een waarde uit de lijst te kiezen.

... dus helemaal opnieuw beginnen. Heeft er iemand een voorstel hoe ik dit best aanpak.
Ben nu al een paar dagen verloren (maar wel veel geleerd ) voor iets wat met het originele xls-bestand wel perfect lukte.
Lang leve de xlsm-vooruitgang :evil: !!!

Groetjes,
Marnik

 

[HSV]

Zet onderstaande eens in bladmodule 'Blad1'.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1:A9")) Is Nothing Then
   If Target.Validation.Value = False Then MsgBox "Foute waarde"
   End If
End Sub

 

[willema]

Dag Harry,

Dit werkte in eerste instantie.
Maar bij heropstart komt er een fout in de eerste code die je opgaf (Workbook_Open). Zie bijlage.
En als je die code weg haalt, dan werkt het weer niet meer bij opstarten.

PS.: Ik test nu ook een piste met Workbook_Change in combinatie met Workbook_SelectionChange.
Als hier iets zinvols uitkomt, laat ik het u weten via deze weg.

Groeten,
Marnik

 

[HSV]

Dag Marnik,

Bij het openen kreeg ik ook een foutmelding, maar eenmaal alles goed ingesteld en opgeslagen en wederom openen niet meer.

Verander de code ook nog even daar je target nu leeg maakt.

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    If Not Intersect(Target, Range("A1:A9")) Is Nothing Then
        If Target.Validation.Value = False Then
            MsgBox "Foute waarde"
            Target.Value = ""
        End If
    End If
  Application.EnableEvents = True
End Sub

 

[willema]

Dag Harry,

ik ben net klaar met mijn eigen piste (een combinatie van Workbook_Change en Workbook_SelectionChange).
Dit is zeker niet de meest efficiënte manier maar het werkt en (niet onbelangrijk) ik begrijp wat er gebeurt.

Ik ga zeker, bij wijze van oefening, in een kopie mijn code verwijderen en gaan experimenteren met jouw tips.
Maar dan moet ik wel een oplossing vinden om dit...

Private Sub Workbook_Open()
     Sheets("Basis").Range("C2:C75").Validation.Modify xlValidateList
End Sub

...toe te passen op elk sheet die een kopie is van de basis. De gebruiker kan zo'n kopie om het even welke naam geven, maar ze zijn wel herkenbaar aan het feit dat bvb. in cel A1 de waarde "SC." staat.

Groeten,
Marnik

PS.: Fijn om u wat beter te leren kennen via de Helpmij.nl-nieuwsbrief :thumb:

 

[HSV]

Hallo Marnik,

Onderstaande code is voor elk werkblad in het werkboek.

Private Sub Workbook_Open()
dim sh as worksheet
    'on error resume next
 for each sh in sheets
  sh.Range("C2:C75").Validation.Modify xlValidateList
 next sh
End Sub

Mocht er in een bepaald blad geen validatielijst zijn, kun je de apostrof voor "On Error Resume Next" weghalen om foutmeldingen te voorkomen.

Ps. Leuk he, ik vind het wel een eer om geïnterviewd te worden; zo lees je nog eens wat over de achtergrond van een helper, maar schrijf maar gewoon "jij" hoor. :thumb: