XL 2019 Vérification de l'utilisation d'un menu

[Sheldor]

Bonjour à tous,
j'ai besoin de vérifier si des cellules contiennent bien les éléments "autorisés" par la validation via une liste car un copier coller peut passer par dessus cette contrainte de saisie.

J'étais parti sur quelque chose sous la forme Cells(aa, bb).Validation.Value en true ou false mais j'ai eu des cas où certains éléments n'étaient pas détectés sans que j'arrive à m'expliquer pourquoi.
En plus ça ne détecte pas (d'après mes tests en tout cas) une différence de casse.

je souhaiterais donc changer de méthode et charger les cellules dans un array ou un dico pour que ça me sorte rapidement le résultat (j'ai 500 colonnes concernées et une centaine de listes) mais, débutant, je n'ai pas réussi à adapter des codes pour y arriver....

merci d'avance pour votre aide

 

[sylvanu]

Bonjour Sheldor,
Une possibilité :
1- Appeler dans ma_liste la plage de données : Liste.
2- Dans Mes_données mettre une MFC en I3 avec :

=ET($I3<>"";NB.SI(Liste;$I3)=0)
et s'applique à =$I$3:$I$28

 

[Sheldor]

Bonjour Sylvanu,
merci beaucoup pour cette réponse, je ne savais pas qu'on pouvait appeler la liste dans une mefc, super intéressant et ça me servira pour d'autres choses.

Pour mon "problème" je voudrais le faire par VBA pour que ça me sorte la réponse dans une feuille "résultat" car je peux avoir des colonnes avec des milliers de lignes de remplies, désolé j'aurais dû être plus précis

merci encore

 

[sylvanu]

Re,
Sorry, pas vu pour le VBA.
Un essai en PJ, la macro s'exécute automatiquement lorsqu'on sélectionne la feuille "résultat", avec :

Sub Worksheet_Activate()
    Dim L%, i%, T
    Application.ScreenUpdating = False
    With Sheets("mes_donnees")
        T = .Range("I1:I" & .[I50000].End(xlUp).Row)
    End With
    [G3:H1000].ClearContents: L = 3
    For i = 3 To UBound(T)
        If Application.CountIf([Liste], T(i, 1)) = 0 And T(i, 1) <> "" Then
            Cells(L, "G") = i
            Cells(L, "H") = T(i, 1)
            L = L + 1
        End If
    Next i
End Sub

 

[Sheldor]

re - bonjour,

très grand merci Sylvanu, c'est génial.

 

[Sheldor]

Bonjour, suite...
j'adapte ce code pour le faire tourner dans une boucle, le nom de ma liste change et j'aimerais remplacer [Liste] par une variable nom_menu mais si j'enlève les crochets ça ne fonctionne plus.. je ne trouve pas la bonne syntaxe

merci

 

[sylvanu]

Bonjour,

mais si j'enlève les crochets ça ne fonctionne plus

Normal, "Liste" est une variable VBA, [Liste] est le nom d'une plage dans la feuille.
Si votre liste est modifiable, et pour être tranquille faites Set Liste = Sheets("ma_liste").[H2:H1000] :

Sub Worksheet_Activate()
    Dim L%, i%, T
    Application.ScreenUpdating = False
    With Sheets("mes_donnees")
        T = .Range("I1:I" & .[I50000].End(xlUp).Row)
    End With
    [G3:H1000].ClearContents: L = 3
    Set Liste = Sheets("ma_liste").[H2:H1000]
    For i = 3 To UBound(T)
        If Application.CountIf(Liste, T(i, 1)) = 0 And T(i, 1) <> "" Then
            Cells(L, "G") = i
            Cells(L, "H") = T(i, 1)
            L = L + 1
        End If
    Next i
End Sub

 

[Sheldor]

merci Sylvanu, le code dans la feuille resultat ne se déclenche plus quand on sélectionne cette feuille, si ça ne vient pas du code ça vient peut être de mon ordi... je vais tester ça

 

[sylvanu]

Sur le mien, pas de souci. De toute façon la macro n'a pas changer sur ce point.
Une solution peut être Alt+Ctrl+Supp

 

[Sheldor]

après redémarrage de l'ordi ça fonctionne très bien.
je me demande si ça vient de la stabilité d'excel, de mon ordi, de la quantité de macros complémentaires en fonction ou tout ça mélangé...

 

[Sheldor]

mon rêve serait de récupérer le nom de la liste sur la première cellule par un truc style
Mid(Cells(3,9).Validation.Formula1, 2)
et l'utiliser ensuite comme variable ...

 

[sylvanu]

Re,

le code dans la feuille resultat ne se déclenche plus quand on sélectionne cette feuille

Regardez dans toutes vos macros si vous n'avez pas un "Application.EnableEvents = False"
Il arrive qu'en bricolant on interrompe une macro, que ce flag soit False et qu'il n'est par repassé à True à cause de l'arrêt. Dans ce cas aucune macro événementielle ne peut être déclenchée.

récupérer le nom de la liste sur la première cellule

Si vous utilisez ajuster automatiquement la Liste à la plage, vous pouvez faire :

    With Sheets("ma_liste")
        DL = .[H10000].End(xlUp).Row
        .Names("Liste").RefersToR1C1 = "=ma_liste!R2C8:R" & DL & "C8"
    End With

Quand à récupérer le nom de la liste dans une cellule, dans ce cas elle s'appellerait toujours Liste.

 

[sylvanu]

Une autre approche. Dans Gestionnaire de nom vous ajoutez Liste avec comme définition :

=DECALER(ma_liste!$H$2; 0; 0; NBVAL(ma_liste!$H:$H); 1)

Le nom "Liste" est utilisable dans vos formules, et sa dimension sera toujours exactement la liste définie.

 

[Sheldor]

Merci Sylvanu,
je vais reprendre ça demain matin, là je me suis un peu perdu...

 

[Sheldor]

rebonjour,
je suis resté coincé sur :

With Sheets("ma_liste")
DL = .[H10000].End(xlUp).Row
.Names("Liste").RefersToR1C1 = "=ma_liste!R2C8:R" & DL & "C8"
End With

erreur définie par l'application ou par l'objet

j'ai réussi à m'en sortir avec quelque chose de la forme Set liste = Range(Cells(11, COL), Cells(r33, COL))
qui me permet de jouer facilement sur l'emplacement de ma liste

après être resté bloqué longtemps sur :
Set liste = sheets("ma_liste").Range(Cells(11, COL), Cells(r33, COL))
il faut que je sois dans la feuille ma_liste, lui demander de définir ma liste à "distance" ne lui plait pas...

merci beaucoup Sylvanu pour votre aide !