XL 2019 VBA - Problème ComboBox en cascade

[bapt0201]

Bonjour le forum,

Je travaille sur la réalisation d'une petite application a partir d'un Userform mais étant novice en VBA j'ai de grosses difficultés, en cherchant un peu j'ai trouvé des réponses similaires mais je n'arrive pas a les adapter. Je me tourne donc vers vous en espérant que vous puissiez m'aider.

L'objectif est de récupérer les données d'une feuille "Base de données VBA" puis les mettre en forme différemment selon la feuille "structure fichier" pour pouvoir réaliser des calculs pour des reportings. (je n'en suis pas la pour le moment)

Je pensais dans un premier temps utiliser des combobox pour sélectionner mes critères de recherche :
- Le 1er problème, c'est que je n'ai pas réussi a les alimenter en cascade, notamment les combobox territoire et département
- Le deuxième problème, c'est que mon combobox pour les offres et les couts sont alimentés en ligne( les en tête du tableau a partir de la colonne L) et je souhaiterais masquer les colonnes qui n'ont pas été sélectionnées. (comme un filtre en ligne mais dans ce cas pour les colonnes)

Voici le fichier simplifié car un peu lourd, cela sera sans doute plus parlant

Il y a peut-être un moyen plus simple pour le faire ou l'optimiser donc n'hésitez pas a me dire si vous avez d'autre idée.
J'espère que mes explications sont suffisamment claires.

Merci par avance

 

[bapt0201]

Bonjour,

Bonsoir
je asse par là et je lis

quand je dis dans :

"dois je renvoyer la Somme" il faut comprendre le Prix affiché a l'intersection de la Ligne et de la Colonne ici
"1 m3 (Big-bag) Encombrants"
les nombreux exemples que vous nous avez fourni ne m'ont pas aidé Lol
Enfin !
jean marie

D'accord vous aviez bien compris alors, c'est moi qui n'avait pas compris votre réponse

@Dranreb je pensais peut-être utiliser Access pour contourner le problème, par exemple lier les deux bases de données ou mettre les offres en ligne et avoir une grosse BDD dans laquelle je viendrais "piocher" les informations dont j'ai besoin grâce a VBA, pensez vous que ça peut résoudre une partie du problème ?

 

[Dranreb]

Bonjour.
Si la distance à vol d'oiseau est suffisante pour choisir la bonne zone, et si on avait les coordonnées GPS des agences il serait relativement aisé de selectionner la bonne zone de chaque agence par rapport à une une certaine coordonnée indiquée dans des TextBox par exemple. En tout cas beaucoup plus simple que de mélanger des mettres cubes avec d'autres choses qui n'ont rien à voir. Ça ce serait lors de la recherche qu'on les spécifierait dans des ComboBox différentes. La grande force du ComboBoxLiées c'est de savoir trouver automatiquement les lignes portant la combinaison des valeurs spécifiées. Rien à préparer en amont pour cela, il se débrouille !

 

[bapt0201]

Bonjour.
Si la distance à vol d'oiseau est suffisante pour choisir la bonne zone, et si on avait les coordonnées GPS des agences il serait relativement aisé de selectionner la bonne zone de chaque agence par rapport à une une certaine coordonnée indiquée dans des TextBox par exemple. En tout cas beaucoup plus simple que de mélanger des mettres cubes avec d'autres choses qui n'ont rien à voir. Ça ce serait lors de la recherche qu'on les spécifierait dans des ComboBox différentes. La grande force du ComboBoxLiées c'est de savoir trouver automatiquement les lignes portant la combinaison des valeurs spécifiées. Rien à préparer en amont pour cela, il se débrouille !

Ah oui je vois ce que vous voulez dire, ca me parait être une bonne idée mais la distance est kilométrique. j'imagine que ça pose un gros problème ?

Après c'est vrai que je suis assez satisfait du résultat avec les offres sur une colonne, le problème va survenir lorsque je vais intégrer toutes les données... serait ce envisageable de séparer cette grosse BDD en 2 ou 3 fichiers différents et de quand même retrouver toutes les valeurs avec les ComboBoxLiées ?

Je suis désolé si mes questions paraissent "basiques" mais j'ai très peu de visibilité sur VBA a l'état actuel de mon niveau

 

[Dranreb]

On sait calculer en kilomètres la distance à vol d'oiseau entre deux coordonnées GPS. C'est si le tarif dépend de longueurs de routes plus ou moins sinueuses que ça ne va plus. Là il n'y a plus d'autres solution que de disposer de toutes les distances entre tous les couples de lieux pour lesquels on doit pouvoir trouver le meilleur tarif pour une marchandise d'une certaine sorte et d'un certain volume.

 

[Dranreb]

Bonjour.
Cette fonction donne la distance en km entre deux localités connaissant leurs latitudes et longitudes :

Option Explicit
Function DistKm(ByVal Lat1 As Double, ByVal Lon1 As Double, ByVal Lat2 As Double, ByVal Lon2 As Double) As Double
   Lat1 = Rad(Lat1): Lon1 = Rad(Lon1): Lat2 = Rad(Lat2): Lon2 = Rad(Lon2)
   DistKm = ACos(Sin(Lat1) * Sin(Lat2) + Cos(Lat1) * Cos(Lat2) * Cos(Lon1 - Lon2)) * 6371
   End Function
Private Function Rad(ByVal Deg As Double) As Double
   Const K = 14964008 / 857374503: Rad = Deg * K
   End Function
Private Function ACos(ByVal X As Double) As Double
   Const Pi÷2 = 122925461 / 78256779: On Error Resume Next: ACos = Atn(-X / Sqr(1 - X * X)) + Pi÷2
   End Function

 

[ChTi160]

Bonjour ,
Je vois que Bernard essaie de simplifier tout ça et plus ça va moins je comprends Lol
Dans ton fichier du #1 il y'a des colonnes de prestation (M3) il devrait si j'ai bien compris y en avoir une 50 ène pourrais tu chose que tu n'as pas encore fait, donner par rapport a ton fichier le résultat voulu selon les choix .
Je pensais avoir compris , mais apparemment non ! on est arrivé à la recherche de distances via des coordonnées GPS ?????
Bonne journée
Jean marie

 

[Dranreb]

Oui mais ce tableau semble peu pratique. Le classeur joint au #19 a l'air beaucoup plus intéressant et directement exploitable pour des recherches. Le seul truc c'est qu'il a des zones par distances. Donc pour déplacer une chose d'un endroit donné à différentes agences possibles il faut connaitre les coordonnées en latitude et longitudes de cet endroit et des agences pour pouvoir calculer les distances qui les séparent et en déduire le tarif de zone qui s'applique pour chacune.

 

[bapt0201]

Bonjour,

En faite voici le résultat que j'aimerais obtenir. ( J'ai joint le fichier, il vous sera sans doute plus simple de comprendre en exécutant l'Userform )

Lorsque j'intégrerais tous les prestataires au fichier conso je devrais avoir plusieurs lignes par communes (autant que de prestataires) je voudrais alors réaliser une macro supplémentaire qui :

Sur la feuille créée, lors de l'exécution du Userform,
--> balayer la colonne D
--> lorsque le code commune est le même sur la ligne suivante
--> sélectionner les cellules : zone et prix (colonne L,M) de cette ligne et les couper et coller dans les colonnes O et P, de même pour le "Nom d'entreprise" de la colonne A passer à N
--> ensuite de supprimer la ligne --> sinon elle passe a la ligne suivante
--> si la condition était vrai pour la ligne précédente alors je répète le processus pour les colonnes Q, R, S, etc...

L'objectif final étant d'obtenir une ligne par commune avec tous intervenants et de pouvoir comparer leurs prix pour choisir le plus avantageux (et éventuellement pouvoir créer d'autre formule sur la ligne pour faire différente analyse)

Je n'ai pas encore travaillé sur cette macro elle me semble compliqué pour l'instant a réaliser.

Mon première objectif était déjà d'extraire la plage voulu (par territoire, département, une offre)
Du coup en passant les offres sur une ligne et en utilisant les ComboBoxLiées de @Dranreb cette première étape est déjà partiellement résolu et je vous en remercie car vous m'avez beaucoup aidé

Le problème restant étant lorsque je vais devoir utiliser la totalité de ma base de données qui aura donc + de 1m de ligne, je suis entrain de la structurer sur Access ce qui pourrait résoudre ce problème (je n'en suis vraiment pas sur)

Il y a peut être une façon plus simple d'obtenir ce résultat avec les coordonnées GPS par exemple comme vous l'avez mentionné @Dranreb.

J'aurais peut-être du commencer par ces explications mais je n'avais pas la visibilité nécessaire désolé

 

[Dranreb]

La première question qui me vient à l'esprit c'est concernant votre colonne Offre. Elle montre "15 m3 Encombrants". Or je sais, pour l'avoir vu, qu'à la source c'est formé de plusieurs informations distinctes puis concaténées. Pourquoi diable ne les laissez vous pas séparées ? Je vous l'ai déjà dit, ça ne pose pas de problème d'interroger un tableau selon une combinaison de plusieurs caractéristiques différentes spécifiées dans plusieurs ComboBox. Ce qui était inexploitable c'était plusieurs combinaisons toutes faites spécifiées dans plusieurs colonnes.
J'ai l'impression qu'il n'y a pas de progrès. La répétition de colonnes ou de groupes de colonnes servant à la même chose pour des cas différents, ce n'est définitivement pas bon en tant que structure de base de donnée !

Arrêtez de faire des filtres, ça ne sert à rien. Ajoutez cette procédure dans votre UserForm, après la Sub CLs_Change, comme ça, juste pour voir :

Private Sub CLs_Résultat(Lignes() As Long)
   Dim TDon(), Trés(), N As Long, L As Long, C As Long
   TDon = CLs.PlgTablo.Value
   ReDim Trés(1 To UBound(Lignes), 1 To CLs.Colonnes.Count)
   For N = 1 To UBound(Lignes)
      L = Lignes(N)
      For C = 1 To UBound(Trés, 2)
         Trés(N, C) = TDon(L, C): Next C, N
   Feuil4.[A4].Resize(1000, UBound(Trés, 2)).ClearContents
   Feuil4.[A4].Resize(UBound(Lignes), UBound(Trés, 2)).Value = Trés
   End Sub

Les rapports, je les établit d'habitude avec un autre outil: ma fonction Gigogne, et il est aussi possible de lui dire de ne prendre en compte qu'une liste de numéros de lignes, celle la même récupérée par l'évènement Résultat d'un ComboBoxLiées.

 

[bapt0201]

Oui c'est vrai que ca n'a plus d'intérêt maintenant, je l'avais fait comme ca car je n'avais pas votre ComboBoxLiées et je ne l'avais pas modifié. Je vais supprimé les colonnes désormais inutile et rajouter des combobox

Ah oui effectivement ca va mieux avec ce code, le seul inconvénient c'est qu'il sélectionne toutes les lignes dès le début et j'ai peur qu'a cause de ça en utilisant la vrai base de données il rame ? Est ce possible de générer les lignes lors du Private Sub CommandButton_ok_Click()

 

[Dranreb]

Ça vient de ce qu'on ne peut pas pour l'instant avoir toutes les ComboBox vides car il y en a une qui a "Opérable" quelle que soit la ligne, donc c'est assumé, et il y correspond de ce fait l'ensemble des lignes.
Pour plusieurs centaines de milliers de lignes, oui, ça pourrait finir par ramer.

 

[bapt0201]

Ça vient de ce qu'on ne peut pas pour l'instant avoir toutes les ComboBox vides car il y en a une qui a "Opérable" quelle que soit la ligne, donc c'est assumé, et il y correspond de ce fait l'ensemble des lignes.
Pour plusieurs centaines de milliers de lignes, oui, ça pourrait finir par ramer.

D'accord et pour l'instant je peux supprimer cette colonne "opérabilité" et le combobox qui va avec car je n'en ai pas l'utilité, je l'intégrerais plus tard si besoin.
il serait donc possible a ce moment là de ne pas générer toutes les lignes ?

 

[Dranreb]

Oui la Sub CLs_Résultat n'est pas exécutée si NbrLgn est à 0 dans la CLs_Change. Si elle est exécutée la liste des numéros de lignes est établie de toutes façon. Mais on peut la verser dans une Private TLgn() As Long, faire TLgn = Lignes dans la Sub CLs_Résultat en vue de l'exploiter ailleurs avec un code très similaire. On peut aussi garnir une ListBox si c'est simplement la modification systématique des cellules qui vous gène.

 

[bapt0201]

Bonjour désolé pour ma réponse tardive je n'ai pas eu le temps de retravailler sur ce projet cette semaine, je vais essayer ce que vous m'avez dit, l'idée d'une listbox pourrait être envisageable. J'ai retravaillé sur les bases de données pour avoir quelque chose de beaucoup plus pratique. Je vous enverrai un exemplaire lorsque ce sera prêt. Merci pour votre aide.

En faite question qui n'a rien a voir avec ce sujet :
@Dranreb Avez-vous fais d'autres programmes sur VBA comme celui des ComboBoxliées ? Ca m'est vraiment très utile

 

[Dranreb]

Bonjour
J'ai naturellement fait d'autres choses, dont mon CouleurCls.xlsm :

Élaboration et utilisation de jeux de couleurs.

Mise au point facile de jeux de couleurs pour UserForm, Shape et cellules, voire images calculées. Possibilité de capturer un pixel n'importe où sur l'écran. Module de classe Couleur pour utilisation dans d'autres projets VBA.

www.excel-downloads.com

Des tirages aléatoires en tout genre pour compétitions
Une fonction Gigogne qui est très pratique lorsqu'on préfère utiliser vba plutôt que Power Pivot que beaucoup préfèrent.
Regardez aussi les classeurs que je joins aux discussions auxquelles je participe. Leur noms commence souvent par un mot indiquant le thème, CBxLiées, ListeAléat etc. suivi du pseudo du demandeur.