XL 2019 Valiation de donnees.

[xav1944]

Bonjour à tous,

je pense que la question a déjà été posée, mais je n'ai jamais réussi à faire une chose toute simple.

J'ai un tableau à 2 colonnes qui contient en première colonne un code et en deuxieme collone le libellé correspondant.

La question est la suivante :

Je voudrai, soit avec validation des données, mais je n'y suis jamais arrivé, soit en VBA (je ne suis pas très au point),

donc ... je voudrais dans une cellule, pouvoir choisir dans une liste de validation, le code a utiliser, mais avec présentation dans la liste déroulante, à la fois le code mais également le libellé.

Autrement dit, selectionner le code à partir du libelle associé.
Dans la liste on présente à la fois le code et le libellé, la selection ne récupère que le code dans la cellule concernée.

Un grand merci si on peut me dépanner.

 

[Franc58]

Bonsoir, voici une proposition.
Supposons que les codes soient en colonne A et les libellés en colonne B. En colonne C on combine les valeurs des 2 colonnes avec la formule =A2 & " - " & B2
En E2 par exemple on crée la liste déroulante avec comme source C2 à Cx.
On utilise la macro ci-dessous à insérer dans le code de la feuille.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    
    ' Spécifiez la cellule ou la plage de cellules contenant la liste déroulante
    Set rng = Range("E2:E10")
    
    ' Vérifiez si la cellule modifiée est dans la plage spécifiée
    If Not Intersect(Target, rng) Is Nothing Then
        ' Désactive les événements pour éviter une boucle infinie
        Application.EnableEvents = False
        
        ' Parcourt chaque cellule de la plage modifiée
        For Each cell In Target
            If cell.Value <> "" Then
                ' Sépare le code du libellé
                cell.Value = Split(cell.Value, " - ")(0)
            End If
        Next cell
        
        ' Réactive les événements
        Application.EnableEvents = True
    End If
End Sub

 

[xav1944]

Bonsoir, voici une proposition.
Supposons que les codes soient en colonne A et les libellés en colonne B. En colonne C on combine les valeurs des 2 colonnes avec la formule =A2 & " - " & B2
En E2 par exemple on crée la liste déroulante avec comme source C2 à Cx.
On utilise la macro ci-dessous à insérer dans le code de la feuille.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
   
    ' Spécifiez la cellule ou la plage de cellules contenant la liste déroulante
    Set rng = Range("E2:E10")
   
    ' Vérifiez si la cellule modifiée est dans la plage spécifiée
    If Not Intersect(Target, rng) Is Nothing Then
        ' Désactive les événements pour éviter une boucle infinie
        Application.EnableEvents = False
       
        ' Parcourt chaque cellule de la plage modifiée
        For Each cell In Target
            If cell.Value <> "" Then
                ' Sépare le code du libellé
                cell.Value = Split(cell.Value, " - ")(0)
            End If
        Next cell
       
        ' Réactive les événements
        Application.EnableEvents = True
    End If
End Sub

Cela fonctionne parfaitement, par contre, comment faire comme pour une liste déroulante classique ou l'on peut à la fois ; soit saisir la valeur directement si on la connait, soit utiliser la liste déroulante pour effectuer la saisie.
J'abuse peut etre, en effectuant cette demande?

Cordialement xav1944

 

[Franc58]

Voici comment faire:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    Dim codeList As Range
    Dim isValidCode As Boolean
    Dim code As Variant
    
    ' Spécifiez la cellule ou la plage de cellules contenant la liste déroulante
    Set rng = Range("E2:E10")
    ' Spécifiez la plage contenant les codes valides
    Set codeList = Range("A2:A10")
    
    ' Vérifiez si la cellule modifiée est dans la plage spécifiée
    If Not Intersect(Target, rng) Is Nothing Then
        ' Désactive les événements pour éviter une boucle infinie
        Application.EnableEvents = False
        
        ' Parcourt chaque cellule de la plage modifiée
        For Each cell In Target
            If cell.Value <> "" Then
                ' Vérifie si la valeur est dans le format "code - libellé"
                If InStr(cell.Value, " - ") > 0 Then
                    ' Sépare le code du libellé
                    cell.Value = Split(cell.Value, " - ")(0)
                Else
                    ' Vérifie si la valeur entrée manuellement est un code valide
                    isValidCode = False
                    For Each code In codeList
                        If cell.Value = code.Value Then
                            isValidCode = True
                            Exit For
                        End If
                    Next code
                    
                    ' Si le code n'est pas valide, réinitialise la cellule
                    If Not isValidCode Then
                        MsgBox "Le code entré n'est pas valide. Veuillez saisir un code valide ou choisir dans la liste déroulante.", vbExclamation
                        cell.Value = ""
                    End If
                End If
            End If
        Next cell
        
        ' Réactive les événements
        Application.EnableEvents = True
    End If
End Sub

Evidemment, si tu ajoutes des codes, tu devras adapter les Range dans la macro, ce qui n'est pas la meilleure façon de faire. Pour éviter cela il vaut mieux utiliser des plages dynamiques comme source de données. Je te laisse creuser la question.

 

[xav1944]

Bonjour Franc58,

J'ai toujours le message d'erreur de validation d'excel.

Je mets le fichier de test en pièce jointe.

Dans feuil1 le premier code qui fonctionne très bien.
Dans feuil2, le deuxième code qui me renvoie le message d'erreur.

Cordialement xav1944

PS :

Classeur1 est fait avec excel 2010
Classeur2 avec excel 2019
Pas de difference d'execution

 

[xav1944]

Voici classeur2.

 

[Franc58]

J'ai testé les 2 feuilles des 2 classeurs et je ne rencontre aucun problème !?

 

[xav1944]

Bonsoir Franc58,

Je mets en piece jointe le message d'erreur que j'ai.
Je suis sous Windows 10 Pro 22h2.

 

[TooFatBoy]

Bonjour,

Une piste, peut-être :


Et aussi une petite coquille à corriger dans le code.
Coquille qui ne peut se produire si on utilise un Tableau Structuré.

    Set codeList = Sheets("Feuil1").Range("A2:A102")

 

[Franc58]

OK j'ai compris, en fait ce n'est pas vraiment un message d'erreur, cela se produit quand le code entré n'existe pas, il est intercepté par la validation de données et la macro n'est alors pas exécutée, alors que tout se passe bien si le code entré est correct. J'ai bien testé une désactivation de la validation de données dans la macro mais ça ne fonctionne pas car apparemment la validation est prioritaire sur la macro et celle-ci ne s'exécute pas. Je suis désolé mais je ne vois pas comment contourner ce problème.

Edit: après rafraîchissement, je vois la réponse de TooFatBoy, qui me semble une bonne piste.

 

[TooFatBoy]

En pièce jointe le fichier de #8 modifié.

Si quelqu'un peut tester et me dire si le problème est corrigé ou non. Merci par avance.

 

[xav1944]

En pièce jointe le fichier de #8 modifié.

Si quelqu'un peut tester et me dire si le problème est corrigé ou non. Merci par avance.

Bonjour TooFatBoy,

Ca fonctionne très bien des deux façons pour la saisie.

Encore merci à tous.

 

[xav1944]

Bonjour TooFatBoy,

Il ne faut pas décocher la case "Alerte erreur" dans validation des données, car dans cd cas n'importe quelle valeur peut etre entree.
J 'ai donc recoché la case.
Le problème venait en effet de Sheets(feuil1) non indiqué.

Cordialement xav1944

 

[TooFatBoy]

Il ne faut pas décocher la case "Alerte erreur" dans validation des données, car dans cd cas n'importe quelle valeur peut etre entree.

Bizare, car normalement, si la valeur saisie n'est pas dans la liste, la macro doit t'afficher un message d'alerte et rejeter la saisie.