Liste déroulantes liées dans un autre fichier

[Anthony53]

Bonjour à tous,
Pour mon premier post, je m'en remet à vous pour solutionner un problème de taille pour moi:

J'ai un 1er fichier qui me sert de base de données avec plusieurs colonnes (Code produit, désignation, fournisseur, contrôles à effectuer sur les produits). Ce fichier compte environ 2 000 lignes.

J'ai un autre fichier destiné à servir de formulaire pour renseigner les résultats des différents contrôles effectués sur les produits.
Dans ce formulaire j'aimerai avoir une cellule avec une liste de choix me permettant de sélectionner ou de saisir le code produit du fichier BDD. Dans une autre cellule j'aimerai qu'en fonction du contenu de la cellule précédente, le fournisseur concerné puisse être sélectionné car pour un même code produit je peux avoir plusieurs fournisseurs.

Pour résoudre ce casse tête, j'ai pensé à faire des listes de choix liées, mais le truc c'est qu'avec le nombre de codes produits que j'ai, il est n'est pas concevable de nommer les différentes plages de code articles et de fournisseurs. De plus pour que cela fonctionne il faudrait qu'a chaque fois le fichier de BDD soit ouvert, ce que je ne souhaite pas. Et solution peu pratique compte tenu que le fichier BDD évolue perpétuellement.

Autre fonctionnalité attendue: je voudrai qu'une fois la sélection du code produit et du fournisseur effectuée, je puisse effectuer une recherche dans la ligne concernée de la BDD (identifiée de manière unique avec les critères code produit + fournisseur) pour indiquer dans mon formulaire les contrôles à effectuer sur le produit.

Pour le moment les solutions que j'ai pu voir sur le forum sont destinées à des fichiers avec peu de données. Je penses que la solution est un code VBA, mais je ne sais pas du tout par où commencer étant donnés mes critères. De plus mon fichier de BDD est destiné à évoluer (ajout de ligne, modifications de données), il est donc indispensable que le code prenne en compte les nouvelles données.

Pour le moment les solutions que j'ai pu voir sur le forum sont destinées à des fichiers avec peu de données, pas ou peu évolutives. Si quelqu'un avait quelques pistes et idées de code VBA pour m'aider ce serait de la plus grande utilité. N'hésitez pas non plus à me dire si ce n'est pas réalisable!

Merci par avance

Anthony

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Bonjour Anthony53, bienvenue sur XLD,

Ben vous alors... Vous voulez que ça dise papa maman mais bouche cousue...

De plus pour que cela fonctionne il faudrait qu'a chaque fois le fichier de BDD soit ouvert, ce que je ne souhaite pas.

Eh oui il faudra. Mais on peut ouvrir le fichier, prendre les infos nécessaire et le refermer immédiatement.

Autre fonctionnalité attendue: je voudrai qu'une fois la sélection du code produit et du fournisseur effectuée, je puisse effectuer une recherche dans la ligne concernée de la BDD(...)

Raison de plus pour ouvrir le fichier !!

Je penses que la solution est un code VBA, mais je ne sais pas du tout par où commencer étant donnés mes critères.

Et nous, sans fichier encore moins... Mais vous pensez bien

N'hésitez pas non plus à me dire si ce n'est pas réalisable!

Vous plaisantez j'espère. Si vos fichiers sont bien fichus, on acceptera de se donner du mal. Autrement on laissera tomber.

A+

 

[BOISGONTIER]

Re : Liste déroulantes liées dans un autre fichier

Bonjour,

Exemple avec ADO

http://boisgontierjacques.free.fr/pages_site/donneesvalidation.htm#ado
http://boisgontierjacques.free.fr/pages_site/listes_cascade.htm#CascADO

JB

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Bonsoir JB,

Merci de rappeler qu'en effet il y a ADO qui permet de travailler sur un fichier fermé.

Et sur un gros fichier on doit gagner du temps.

Perso je n'ai jamais utilisé cette méthode, c'est une lacune.

Si notre ami nous envoyait ses fichiers (il n'a pas l'air pressé), il serait intéressant de comparer les 2 possibilités.

Edit : je pars en week-end et ne serai de retour que mardi 19/10. D'ici-là...

A+

 

[Anthony53]

Re : Liste déroulantes liées dans un autre fichier

Bonjour,

Je vous joins les fichiers en question pour vous permettre de comprendre un peu mieux ma demande. Pour des raisons de confidentialité, les noms et désignations des fichiers ont été modifiés. J'ai également supprimé une partie du fichier BDD pour que je puisse le mettre sur le forum.

Merci de votre aide

 

[BOISGONTIER]

Re : Liste déroulantes liées dans un autre fichier

Bonsoir,

-Ne pas utiliser de caractères spéciaux dans les noms de fichiers
-Ne pas utiliser de cellules fusionnées

http://boisgontierjacques.free.fr/fichiers/BDD MP.zip

http://boisgontierjacques.free.fr/pages_site/listes_cascade.htm#ADOFourn

Les codes sont triés par code.
Pour un tri par désignation:

SELECT [Code Produit],[Désignation MP] FROM BD WHERE [Code Produit]<>'' group BY [Code Produit],[Désignation MP] Order By [Désignation MP]

Dim répertoire
Dim fichier
Private Sub UserForm_Initialize()
  'Microsoft ActiveX Data Object 2.8 doit être activé
  Dim rs As ADODB.Recordset
  Set cnn = New ADODB.Connection
  répertoire = ThisWorkbook.Path & "\"
  fichier = "BDD MP.xls"
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & répertoire & fichier
  Set rs = cnn.Execute("SELECT [Code Produit],[Désignation MP] FROM BD WHERE [Code Produit]<>'' group BY [Code Produit],[Désignation MP]")
  Me.ComboBox1.List = Application.Transpose(rs.GetRows)
  rs.Close
  cnn.Close
  Set rs = Nothing
  Set cnn = Nothing
  SendKeys "{F4}"
End Sub

Private Sub ComboBox1_Change()
  Dim rs As ADODB.Recordset
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & répertoire & fichier
  Set rs = cnn.Execute("SELECT [Désignation fournisseur] FROM BD WHERE [Code Produit]='" & Me.ComboBox1 & "'")
  Me.ComboBox2.List = Application.Transpose(rs.GetRows)
  rs.Close
  cnn.Close
  Set rs = Nothing
  Set cnn = Nothing
  Me.ComboBox2.SetFocus
End Sub

Private Sub ComboBox2_Change()
  ActiveCell = Me.ComboBox1
  ActiveCell.Offset(1) = Me.ComboBox2
  ActiveCell.Offset(2) = Me.ComboBox1.Column(1)
  Unload Me
End Sub

JB

 

[Anthony53]

Re : Liste déroulantes liées dans un autre fichier

Bonjour,

Merci pour les réponses qui sont très utiles. Les approches de BOISGONTIER et JEAN-MARCEL sont différentes mais très proches au niveau du résultat et me conviennent parfaitement. Une petite chose subsiste, c'est que lorsque je rajoute des lignes la BDD, elle n'apparaissent pas dans la combo box de l'autre fichier, et je vois pas d'où cela peut provenir (les codes prennent bien en compte toutes les lignes du fichier). Si vous avez une idée...

En tous cas le principe correspond entièrement à mes attentes et je ne pensais pas avoir des réponses si rapides!

Merci, a+

 

[Anthony53]

Re : Liste déroulantes liées dans un autre fichier

Ok j'ai trouvé d'où cela venait. C'est le fait que les nombre du code produit de ma BDD sont stockés au format texte. Donc lors que je rajoute un code produit, il faut que je rajoutes un apostrophe devant pour qu'il apparaissent dans la combobox de l'autre fichier.

Du coup, y a t'il un moyen pour ne pas avoir à rajouter l'apostrophe lors de la saisie d'un nouveau produit dans la BDD pour éviter les problèmes de saisie? Il n'y aura pas que moi à utiliser ces fichiers, donc je crains très fortement que certains n'y penseront jamais et me reprochent que ça ne fonctionne pas!

Après cela, je penses que tout sera ok pour la phase application! MERCI!

 

[Anthony53]

Re : Liste déroulantes liées dans un autre fichier

Bonjour,

Merci Jean-Marcel, j'ai réussi à adapter les codes à mes vrais fichiers sans difficultés. Cependant j'aurai une dernière faveur à te demander: est-il possible de trier les codes produits dans l'orde?

J'ai trouvé pas mal de réponses dans le forum mais je ne vois pas du tout comment les adapter à mon code... Moi qui pensais commencer à connaître le VB, avec ce cas, je me sens bien ridicule...

Merci encore pour ton aide précieuse.

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Bonjour Anthony53, JB, Jean-Marcel,

Voici une solution qui utilise des listes de validation.

Télécharger le fichier zip et extraire les 2 fichiers vers le même répertoire.

1) Fichier BDD MP.xls :

- en A2 se trouve la date du dernier enregistrement

- cette date est entrée par la macro évènementielle se trouvant dans ThisWorkbook.

2) Fichier Contrôle MP(1).xls :

- une feuille BDD a été ajoutée avec en D2 la liaison vers la cellule A2 du 1)

- dans Module1 la macro MAJ permet d'alimenter la feuille BDD quand nécessaire

- dans ThisWorkbook, 2 macros qui se déclenchent à l'ouverture et à la fermeture du fichier

- dans le code de la feuille MP et Négoce, 2 macros évènementielles, la 2ème renseignant les cellules B8 et B9.

- bien comprendre les 2 noms définis Liste1 et Liste2 pour les listes de validation en B7 et B8.

Nota : j'ai dû alléger les 2 fichiers pour pouvoir les déposer ici.

A+

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Re,

Suite à mon post #14, une précision.

Dans le fichier BDD MP.xls, j'ai mis la colonne A au format Texte, de cette manière il est inutile de mettre des apostrophes devant les codes.

En recherchant les nombres dans cette colonne (touche F5), 3 codes n'étaient pas du texte, il a suffit de revalider pour les transformer en texte.

De cette façon le tri alphabétique de BDD effectué par la macro MAJ se fera toujours correctement.

A+

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Bonsoir à tous,

Une version (2).

Il est plus logique, et plus sûr, de définir d'une manière permanente le nom Liste2 par la formule :

=DECALER(BDD!$C$3;EQUIV('MP et Négoce'!$B$7;Liste1;0);;1+EQUIV('MP et Négoce'!$B$7;Liste1)-EQUIV('MP et Négoce'!$B$7;Liste1;0))

Sur cette version, si B7 est vide (effacé), la liste de validation en B8 est bloquée.

A+

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Bonjour Anthony53,

Je ne m'étais pas préoccupé des autres contrôles, cellules B24 à B32.

La version (3) ci-jointe maintenant les renseigne.

C'est donc la plage A:M du fichier BDD MP.xls qui est copiée sur la feuille BDD.

La cellule contenant la liaison est BDD!N2.

J'ai aussi ajouté une petite amélioration : la mise à jour n'a lieu que si le fichier BDD MP.xls n'est pas en cours de modification, il doit être Saved :

If Workbooks(fichier).Saved Then Workbooks(fichier).Sheets(F).[A:M].Copy .[A1]

Nota : apparemment il n'y a rien pour renseigner la cellule B29 "Taux de rebut"...

A+

 

[job75]

Re : Liste déroulantes liées dans un autre fichier

Re,

JB doit rire car l'accouchement est un peu laborieux...

Voici une version (4) nettement meilleure.

Elle permet de ne pas écrire "en dur" la date d'enregistrement sur les fichiers : plus de macro dans le fichier BDD MP.xls, ni de liaison.

Elle utilise l'objet "Scripting.FileSystemObject". La macro MAJ du Module1 :

[COLOR="Red"]Dim DateEnregistrement As Date 'mémorise la date et l'heure[/COLOR]

Sub MAJ()
Dim chemin$, fichier$, F$, o As Object, dat As Date, ouv As Boolean
chemin = ThisWorkbook.Path & "\"
fichier = "BDD MP.xls"
F = "Dossiers fournisseurs"
[COLOR="red"]Set o = CreateObject("Scripting.FileSystemObject")
dat = o.GetFile(chemin & fichier).DateLastModified 'date du dernier enregistrement[/COLOR]
If DateEnregistrement <> dat Then 'si la mise à jour est nécessaire
  On Error Resume Next
  If IsError(Workbooks(fichier).Name) Then 'si le fichier n'est pas ouvert
    Application.ScreenUpdating = False
    Workbooks.Open chemin & fichier
    ouv = True
  End If
  On Error GoTo 0
  With ThisWorkbook.Sheets("BDD")
    If Workbooks(fichier).Saved Then 'si le fichier n'est pas en cours de modification
      Workbooks(fichier).Sheets(F).Cells.Copy .[A1]
      [COLOR="red"]DateEnregistrement = dat[/COLOR]
    End If
    If ouv Then Workbooks(fichier).Close False
    .[A3:M65536].Sort Key1:=.[A3], Order1:=xlAscending, Key2:=.[C3], Order2:=xlAscending, Header:=xlYes 'tri de BDD par codes croissants
  End With
End If
Set o = Nothing
End Sub

A+