Validation des données dynamique double condition

Bonjour à tous,

J'ai une question à laquelle je n'ai trouvé aucune réponse dans mes recherches personnelles (je suis dessus depuis 2 jours).

Commençons par le commencement, j'ai :

• Une base de données ACCESS contenant 7 tables, il s'agit de locations d'instruments, chaque table correspond à un type d'instrument (violon, piano etc ...). Cette séparation est nécessaire vu le nombre d'instruments à disposition.
• Un document EXCEL, avec une feuille par type d'instrument, qui importe la base de données ACCESS.
• Sur ce document, j'ai également le contrat de location, où figurent la référence de l'instrument, son type, et le détail (N° de série, modèle etc ...)

Mon problème est le suivant :

J'aimerais que dans le contrat de location, je puisse sélectionner la référence dans une liste déroulante mais seulement des produits du type sélectionné auparavant ET seulement les références disponibles.

Mes tableaux importés d'ACCESS comportent : la référence, la désignation, le loyer, la caution et la disponibilité. La première colonne est nommée afin qu'EXCEL rajoute automatiquement les nouvelles références sans avoir à tout modifier.

J'arrive jusqu'à présent à poser la condition pour afficher les références selon le type d'instrument, ce qui est assez facile, mais je n'arrive pas à rajouter la deuxième condition et ne sais même pas si c'est vraiment possible compte tenu de la configuration de mon fichier.

Je vous remercie pour votre aide, si je n'ai pas été assez clair/précis, n'hésitez pas

Imha

Bonjour,

Pour les références disponibles, je suppose qu'il y a un champ particulier pour indiquer si l'instrument en question est loué ou en stock ?

Oui, il y a une colonne "DISPO" sur tous les tableaux avec deux possibilités "OUI" ou "NON".

J'aimerais que dans le contrat de location, je puisse sélectionner la référence dans une liste déroulante mais seulement des produits du type sélectionné auparavant ET seulement les références disponibles.

Donc, il faut remplir ta liste de références en fonction des valeurs Oui/Non de ta colonne "Dispo" qui les concernent !

Postes un classeur exemple le plus fidèle possible sans données confidentielles pour éviter de taper à coté

Voici le document test sur lequel je travaille, prenez en compte que les feuilles contenant les instruments sont normalement en relation avec une BDD ACCESS qui sera actualisée.

Je te retourne ton classeur dans lequel j'ai ajouté une procédure événementielle "Worksheet_Change()" qui va créer une liste de validation dans la cellule B15 en fonction du choix de la cellule E2, un choix dans la liste de B15 va entrer la référence de l'instrument choisi en B16.

Pour que cette liste de validation puisse être "dynamique" (fonction du choix du type d'instruments et des disponibilités), j'ai ajouté une feuille "FeuilListes" qui reçois, pour l'instant la liste de choix devant apparaître en B15. Cette feuille peut être masquée (Format, Masquer & Afficher,...)

Pour que ça fonctionne pour tous les instruments, les tableaux doivent être de structure identique (même nombre de colonnes et même organisation)

Super ce que tu as fait ça marche nickel, une question cependant (C'est sûrement possible mais je serais je pense incapable de modifier ton code proprement), est-il possible de mettre la liste déroulante sur la case REF. toute en haut, de faire ressortir à partir de là les références (et pas la désignation) et de remplir de ce fait la désignation ? (en gros, tout inverser, et plutôt que de remplir "Numéro" remplir REF., Numéro faisant référence au N° de série). Autre chose : des colonnes vont être ajoutées (pour remplir les cases manquantes qui n'étaient pas nécessaire à ça au début, comme le N° de série), toutes les feuilles vont avoir les mêmes colonnes mais pendant que je vais les ajouter cela ne posera pas de problème ? Une actualisation des données venant d'ACCESS se passera bien aussi ? Et enfin, quelles lignes de code rajouter pour également ajouter automatiquement le loyer, la caution, et la valeur de l'instrument plus bas ?

Je suis désolé d'en rajouter un peu, si tu n'as pas le temps laisse faire, si je me penche sur ce que tu as fais quelques heures je devrais trouver mon bonheur Merci encore.

Bonjour,

Pour ta première question :

est-il possible de mettre la liste déroulante sur la case REF. toute en haut, de faire ressortir à partir de là les références (et pas la désignation) et de remplir de ce fait la désignation ? (en gros, tout inverser, et plutôt que de remplir "Numéro" remplir REF., Numéro faisant référence au N° de série)

voici le code modifié avec les commentaires pour la compréhension :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Plage As Range
    Dim Cel As Range
    Dim Tbl() As String
    Dim I As Integer

    'si la modification de valeura lieu en cellule E2...
    If Target.Address(0, 0) = "E2" Then

        'défini la plage de recherche sur la feuille concernant le type d'instrument choisi colonne E à partir de E2
        With Worksheets(Target.Value): Set Plage = .Range(.Cells(2, 5), .Cells(.Rows.Count, 5).End(xlUp)): End With

        'boucle sur la plage et stocke dans le tableau la référence des instruments disponibles
        'qui se trouve 4 colonnes à gauche "Offset(, -4)" donc en colonne A
        For Each Cel In Plage

            If UCase(Cel.Value) = "OUI" Then

                I = I + 1: ReDim Preserve Tbl(1 To I)
                Tbl(I) = Cel.Offset(, -4).Value

            End If

        Next Cel

        'si le tableau a été initialisé, colle celui-ci dans la colonne A de la feuille "FeuilListes" à partir de A1
        If Not Not Tbl() Then

            With Worksheets("FeuilListes")

                On Error Resume Next
                ThisWorkbook.Names("LstInstruments").Delete 'supprime le nom
                On Error GoTo 0

                .Columns(1).Clear 'vide la colonne

                'le tableau étant en ligne pour Excel, "Transpose()" permet la pose des valeurs en colonne
                .Range(.Cells(1, 1), .Cells(UBound(Tbl), 1)) = Application.Transpose(Tbl)
                .Range(.Cells(1, 1), .Cells(UBound(Tbl), 1)).Name = "LstInstruments"

            End With

            'suspend les événements puis vide les cellules
            Application.EnableEvents = False
            Range("B13:17").Value = ""

            'supprime la liste existante et la re-crée
            With Range("E1").Validation

                .Delete
                .Add xlValidateList, , , "=LstInstruments"

            End With

        End If

    End If

    'si la modification de valeura lieu en cellule E1...
    If Target.Address(0, 0) = "E1" Then

        'défini la plage de recherche sur la feuille concernant le type d'instrument choisi colonne A à partir de A2
        With Worksheets(Range("E2").Value): Set Plage = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With

        'recherche la référence dans la plage...
        Set Cel = Plage.Find(Target.Value, , xlValues, xlWhole)

        '...si trouvée, inscrit la désignation (qui se trouve juste à coté d'où le décalage "Offset(, 1)") en cellule B15
        If Not Cel Is Nothing Then

            Application.EnableEvents = False
            Range("B15").Value = Cel.Offset(, 1).Value

        End If

    End If

    'rétabli d'office le gestionnaire d'événements
    Application.EnableEvents = True

End Sub

Pour ta seconde question :

Autre chose : des colonnes vont être ajoutées (pour remplir les cases manquantes qui n'étaient pas nécessaire à ça au début, comme le N° de série), toutes les feuilles vont avoir les mêmes colonnes mais pendant que je vais les ajouter cela ne posera pas de problème ? Une actualisation des données venant d'ACCESS se passera bien aussi ? Et enfin, quelles lignes de code rajouter pour également ajouter automatiquement le loyer, la caution, et la valeur de l'instrument plus bas ?

Là, il faudrait que tu postes un exemple de ton fichier fini avec toutes les colonnes et des valeurs dans les cellules pour modifier le code et effectuer des tests car forcément il ne conviendra plus !

Quant au fonctionnement avec la liaison Access, seul toi pourras tester et me dire ce qu'il en est car je n'ai pas ta base de données !

Merci d'avoir ajouté les commentaires en plus, c'est beaucoup plus clair !

Je finis le tableau dès que possible et je le poste dès que c'est fait (sûrement dans l'après-midi).

Merci

Salut ! Désolé j'ai eu d'autres priorités avant et après ... Week-end

J'ai fini toute ma base de données, j'ai essayé de modifier avec le code que tu m'as donné mais comme je m'en doutais, ça ne marche pas, j'ai l'impression qu'il ne prend même pas en compte la modification dans la cellule E2 c'est sûrement à cause de l'ajout de colonnes. Est-ce que tu aurais le temps d'y jeter à nouveau un coup d'oeil ? Merci

Bonjour,

Voici le classeur modifié :

C'est beau. MERCI BEAUCOUP.

Promis la prochaine fois j'essaye d'assimiler les cours du site pour moins passer pour un con

Bonne journée à toi et merci encore !

Imha

Content de t'avoir aidé