XL 2016 Liste déroulantes VBA trop longues

[paneguilla]

Bonjour le forum,
C'est mon premier post sur ce forum et je n'ai pas trouvé de post qui mentionne mon problème.
Je travaille avec un tableau de suivi de production et lorsque je rentre un numéro de commande sur une ligne, je veux que sur le ligne du dessous, il y ait une liste déroulante avec le choix des produits en fonction du numéro de commande. Tout marchait très bien jusqu'à ce que je me rende compte qu'il y a une limite de taille pour les listes déroulantes. En effet, lorsqu'il y a trop de produits, j'ai ce message d'erreur lorsque j'ouvre mon classeur après.
"Désolé, nous avons trouvé un problème dans le contenu de (mon fichier)."

Lorsque je clique sur Oui, le fichier s'ouvre mais tout mon code a été effacé.

Voici ma fonction qui met la liste déroulante :
Sub ListeProduit(Target As Range)
Dim I, J, K As Integer
For J = 0 To 1
K = 0
For I = 0 To 9
If Not Intersect(Target, Workbooks("planningprodV5.xlsm").Sheets("Planning").Range("B10:AG10").Offset(K, 36 * J)) Is Nothing Then 'Pour vérifier que c'est un numéro de commande qui a été modifié
If Target.Value <> "" Then
With Target.Offset(2, 0).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Creertableau(Target.Value)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
End If
Exit Sub
End If
K = K + 5 + 5 * (I Mod 2)
Next I
Next J
End Sub

La fonction CreerTableau parcourt tous les numéros de produits associés au numéro de commande et les concatène. Je sais que le problème vient de là, car lorsque cela devient trop long, le code ne marche plus.
J'avais essayé de me servir de la fonction "Indirect" mais je n'arrive pas à mettre un nom de variable dedans.

Je vous remercie par avance.
Cordialement,
Guillaume

 

[fanch55]

Bonjour,
Quand vous avez ce message, c'est qu'Excel est dans un état non pleinement fonctionnel dans l'environnement Windows .
Surtout ne pas répondre oui et ne pas enregistrer, vous pouvez perdre tout le VBA .
J'ai été confronté maintes fois à ce type de message et le seul moyen que j'ai trouvé pour le résoudre est de redémarrer le système. Jusqu'à maintenant, le redémarrage résout tout (sans garantie certaine) .

 

[paneguilla]

Bonjour Fanch55,
Cela ne fonctionne pas pour ma part.

 

[sylvanu]

Bonsoir Paneguilla, Fanch,
Si vous avez dépassé la taille maximale autorisée par XL, le seul moyen est de changer de stratégie. Vous ne pourrez pas passer outre le maximum.
Voir maximum autorisé :

Spécifications et limites relatives à Excel - Support Microsoft

Dans Excel 2010, la taille maximale des feuilles de calcul est de 1 048 576 lignes par 16 384 colonnes. Cet article décrit les limites et spécifications des fonctionnalités, des classeurs et des feuilles de calcul.

support.microsoft.com

Éléments affichés dans les listes déroulantes de filtrage : max 10000
Mais peut être êtes vous limité par Formula, dans ce cas ce serait 8192 caractères.

Avez vous essayé de déterminer à partir de combien il se plante ?
Il faut calculer en termes de nombre de caractères, et non en nombre de variables.

 

[eriiic]

Bonjour à tous,

tu devrais coller ta liste générée dans une feuille, nommer la plage, et faire ta validation sur ce nom.
Déjà tu n'auras plus l'erreur de dépassement de taille en vba.
Mais si tu as une liste >10000 il va falloir que tu envisages un pré-filtre pour limiter la taille de la liste et pouvoir tout voir.
eric

 

[sylvanu]

Bien le bonjour Paneguilla, Fanch, Eriiic,
Si la liste déroulante à plusieurs centaines de N° alors au point de vue de l'ergonomie c'est discutable.
L'utilisateur risque fort de ne pas apprécier.
Une autre approche ( voir PJ ) est de mettre tous les N° sur une page, et par un jeu de Worksheet_SelectionChange permettre à l'utilisateur de choisir son N° de commande de façon conviviale.

 

[paneguilla]

Bonjour à tous,
Merci pour vos réponses.
La solution de sylvanu me semble adaptée pour mon cas.
Bon week end !

 

[fanch55]

Sans rapport avec les listbox, c'est ce message qui m'était affiché assez souvent, même quand je téléchargeait sur ce forum. Instabilité d'Excel ou de Windows ? Cela ne m'arrive plus depuis la dernière maj de W10 ....