XL 2019 Optimisation d'un code VBA pour liste

[im_Guillaume]

Bonjour le forum,

J'ai actuellement un code qui permet de :
- Dans l'onglet liste : avoir un tableau de données (il va être dynamique, des lignes vont être ajoutées et enlevées dans la version finale)
- dans l'onglet choix : avoir le "catalogue" de chacune des colonnes et pouvoir choisir parmi ces options.

Malheureusement, dans mon document original, le tableau de données dépasse les 500 lignes. Cela m'affiche le message d'erreur suivant :

Puis quand je regarde le fichier journal :
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error104600_01.xml</logFileName><summary>Des erreurs ont été détectées dans le fichier « D:\mailing partenaires 2022 evolve6.xlsm »</summary><removedFeatures><removedFeature>Fonction supprimée: Validation des données dans la partie /xl/worksheets/sheet2.xml</removedFeature></removedFeatures></recoveryLog>

Comme si excel rencontrait un problème avec la validation des données dans l'onglet "choix"...

Je cherche donc à optimiser ce code pour qu'il fonctionne sur de grands tableaux. (Les fonctions doivent rester les mêmes au final)
Il fonctionne actuellement mais uniquement sur les "petits" tableaux.


La version actuelle du code est jointe ci-dessous.

Merci pour toute l'aide que vous pourrez m'apporter!

 

[patricktoulon]

bonjour
je sais pas ce qui se passe chez toi mais chez moi tes listes de validation fonctionnent

un petit détail cependant
si ces liste de validation sont interdépendantes ton principe est incohérent
en effet je choisi 7 dans la liste 1 alors je ne devrais je ne devrais avoir que l’Allemagne dans la liste 2
on peut pas choisir 7 et choisir l’Angleterre hein !!! c'est pas cohérent

 

[im_Guillaume]

Merci pour ton message Patrick,

Oui, le fichier fonctionne bien actuellement, mais je cherche à l'optimiser ou à passer par une autre méthode.

Il ne sont pas dépendant les uns des autres. Nous devons pouvoir choisir parmi toutes les listes à chaque fois, c'est normal

 

[patricktoulon]

re
soit
optimiser je vois pas trop en vba ce que tu peux faire de mieux avec autre que dictionnaire

 

[Dranreb]

Bonjour.
Je pense que vous dépassez la limite de taille du String utilisé pour représenter la liste de cette façon là dans la validation de données.
Je pense que tout devrait être fait via un UserForm muni de ComboBox liées.

 

[patricktoulon]

re
bonjour @Dranreb je pense aussi
lui reste plus qu'a passer par une colonne intermédiaire
au quel cas plus besoins de VBA non plus

 

[im_Guillaume]

Bonjour.
Je pense que vous dépassez la limite de taille du String utilisé pour représenter la liste de cette façon là dans la validation de données.
Je pense que tout devrait être fait via un UserForm muni de ComboBox liées.

Aïe, je ne connais pas les userformet Combobox
Je vais me renseigner, merci pour vos réponses!

 

[patricktoulon]

tu trouvera dans les discussion recentes la substitution d'une liste de validation par un combobox dans les discussion de moins de 3 mois
si tu te donne la peine de chercher tu trouvera , comme ça tu te passe de userform

 

[im_Guillaume]

Voilà le même excel avec un liste trop longue qui fait bugger le document.

Y aurait-il un autre moyen que de passer via des Combobox, juste en modifiant le code VBA?
Je n'ai que 2 jours pour le modifier.

(Le combobox est une fonction qui m'intéresse cependant beaucoup donc je regarderai ça une fois le rush passé!)

Merci encore pour vos réponses,

 

[Dranreb]

Si vous voulez le faire évoluer vers une mise à jour possible de la liste un UserForm serait souhaitable.
Comment, comment, 2 jours ? Ce sera entièrement réglé dans un quart d'heure oui !
En attendant juste une ébauche où les ComboBox ne sont pas liées pour le moment.
D’ailleurs il y a quelque chose qui n'est pas clair dans votre liste: des doublons de l'ensemble des valeurs des 4 colonnes ! Alors je ne sais pas ce que vous voudriez pour la mise à jour en sélectionnant un élément appartenant à une combinaison existant dans plusieurs lignes …

 

[im_Guillaume]

Merci beaucoup Dranreb pour ton message,
Effectivement, un Userform semble adapté. Je ne comprends cependant pas tout dans ton module "MSujetCBx". Tu as du le remplir manuellement ou excel a généré ce module suite à la création de l'userform?

 

[Dranreb]

J'ai glissé/déposé ce module de service depuis le projet VBA de mon CBxLCtlA.xlsm. Il y en aura probablement d'autres par la suite.
Ce classeur existe en téléchargement, muni d'une page d'aide.
Mais je vais vous installer tout ce qu'il faut, dès que vous m'aurez confirmé que chaque ligne des 4 colonnes doit bien former une combinaison valide et surtout éclairci la question des doublons dans la liste.

 

[im_Guillaume]

D'accord, je comprends.
Voilà la version réelle de mon document (plus ou moins vide...)

Nous avons donc :
Une interface permettant de choisir entre éditer des données ou consulter des données.

Nous choisissons de consulter.
Nous arrivons donc sur le "moteur de recherche".

Et c'est là que je souhaiterais avoir la liste des possibilités dans la base de données pour les colonnes :

Département

Type de structure

Structure

Fonction

Titre

Actuellement, le code fonctionne pour des petites listes... l'idéal serait de le passer en Userform.



Pour répondre donc à votre question :
- les doublons ne doivent apparaître qu'une fois dans la liste proposée dans le moteur de recherche
- les listes ne sont pas liées : je dois pouvoir effectuer n'importe quelle recherche et si rien ne correspond dans la base donnée, alors le résultat affiché sera vide.

Voilà l'ensemble des éléments

 

[Dranreb]

Ça, de toute façon, il n'y a jamais de doublon dans les listes construites pas ma fonction SujetCBx utilisées aussi par mon objet ComboBoxLiées. Ah bon ? Les colonnes ne sont pas liées ??? On peut panacher les valeurs de différentes lignes pour différentes colonnes comme on veut ? Ça explique encore moins les doublons dans votre liste !

 

[patricktoulon]

re
voici mon exemple
ca fonctionne comme une liste de validation
dans un module (j'ai viré tout les autres !!)( je le lance dans l'open)

Option Explicit

Sub Createliste()
Dim tbl1(), tbl2(), tbl3(), tbl4(), a&, b&, c&, D&, plage As Range, i&, Dic As Object
Set plage = Feuil2.Range("B3:E" & Feuil2.Cells(Rows.Count, 2).End(xlUp).Row)
Set Dic = CreateObject("Scripting.Dictionary")
With plage

For i = 1 To .Rows.Count

If Not Dic.exists(.Cells(i, 1).Value) Then Dic(.Cells(i, 1).Value) = "": a = a + 1: ReDim Preserve tbl1(1 To a): tbl1(a) = .Cells(i, 1).Value
If Not Dic.exists(.Cells(i, 2).Value) Then Dic(.Cells(i, 2).Value) = "": b = b + 1: ReDim Preserve tbl2(1 To b): tbl2(b) = .Cells(i, 2).Value
If Not Dic.exists(.Cells(i, 3).Value) Then Dic(.Cells(i, 3).Value) = "": c = c + 1: ReDim Preserve tbl3(1 To c): tbl3(c) = .Cells(i, 3).Value
If Not Dic.exists(.Cells(i, 4).Value) Then Dic(.Cells(i, 4).Value) = "": D = D + 1: ReDim Preserve tbl4(1 To D): tbl4(D) = .Cells(i, 4).Value
Next

End With
With Feuil1.OLEObjects("ComboNum").Object: .List = tbl1: .ListIndex = -1: End With
With Feuil1.OLEObjects("ComboPays").Object: .List = tbl2: .ListIndex = -1: End With
With Feuil1.OLEObjects("ComboFonction").Object: .List = tbl3: .ListIndex = -1: End With
With Feuil1.OLEObjects("ComboNom").Object: .List = tbl4: .ListIndex = -1: End With
DoEvents
End Sub

terminé tes combo sont remplies sans doublons


dans le module de la feuille ("choix")

Option Explicit

Private Sub ComboNum_Click(): ActiveCell = ComboNum.Value: invisible: End Sub
Private Sub ComboPays_Click(): ActiveCell = ComboPays.Value: invisible: End Sub
Private Sub ComboFonction_Click(): ActiveCell = ComboFonction.Value: invisible: End Sub
Private Sub ComboNom_Click(): ActiveCell = ComboNom.Value: invisible: End Sub

Sub invisible()
Dim obj
For Each obj In ActiveSheet.OLEObjects
If Left(obj.Name, 5) = "Combo" Then obj.Width = 15:: obj.Height = 15: obj.Visible = False
Next

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
invisible
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
invisible
Select Case Target.Column
Case 2: With ComboNum: .Top = Target.Top: .Visible = True: End With
Case 3: With ComboPays: .Top = Target.Top: .Visible = True: End With
Case 4: With ComboFonction: .Top = Target.Top: .Visible = True: End With
Case 5: With ComboNom: .Top = Target.Top: .Visible = True: End With
End Select

End Sub

terminé elles sont operationelles

démo