Code VBA : Erreur d'exécution 5

Bonjour le forum

J'ai réussi a mettre en place un code, via plusieurs formules trouvées sur internet, permettant de créer des listes déroulantes. Ce code fonctionne pour mes onglets allant de 9 à 27 mais m'affiche une erreur d'exécution 5 si je tente de le faire en onglet 28...

Excel me surligne la ligne suivante : ad1 = Left(ad1, Len(ad1) - 1)

Sachant que mes onglets 9 à 28 sont exactement identiques (j'ai fait un copié/coller) pourquoi m'affiche t'il une erreur ... :/ ?

EDIT : Constat : Qu'importe l'onglet que je nomme Feuil28 : Le code se bloque constamment à partir de 28...

Pourriez vous m'aider s'il vous plait ? Merci beaucoup.

Voici le code :

Sub listeder ()
'Boucle 1
 For B = 9 To 27
 Dl = Worksheets(B).Range("A100").End(xlUp).Row
    Set f = Worksheets(B)

'Boucle 2
    For i = 8 To Dl
    nom = f.Cells(i, 1)
    tb = Split(nom)
    nb = UBound(tb)
    ad1 = ""

'Boucle 3
    For J = 0 To nb
    ad1 = ad1 & tb(J) & "_"
    Next J
    ad1 = Left(ad1, Len(ad1) - 1)
    On Error Resume Next
    With f.Cells(i, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & ad1 
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Sélection"
        .ErrorTitle = ""
        .InputMessage = "Choisissez une valeur"
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With
    On Error GoTo 0
    Next i
    Next B
End Sub

Bonjour

Sans ton fichier pas facile de trouver pourquoi

A tout hasard ad1 est vide

Donc à vérifier quand le plantage à lieu

ad1 est tributaire de nb qui est tributaire de tb qui est tributaire ... etc

Sans fichier cela ne pas pas être facile

bonjour

si tu augmente

'Boucle 1
 For B = 9 To 27

en mettant 28 ou plus est ce que cela change ???

Pascal

Bonjour à vous deux et merci de vous intéressez à mon cas

Banzai64 : Malheureusement je ne peux vous transmettre mon fichier. Ce dernier étant confidentiel. Si je vous le met dans un autre fichier avec des données fictives le code fonctionne...

Grisan29 : Bonjour, si je met :

'Boucle 1
For B = 9 To 28

Le code indique l'erreur

Si je met

'Boucle 1
For B = 9 To 29

le code indique l'erreur... Et ainsi de suite.

Par contre le code ne plante pas lorsque je met 27 ou inférieur...

Chose réellement bizarre c'est que si j'adapte le code spécifiquement à la Feuil28. Celui-ci fonctionne....

Le code est le suivant :

SubelisteAF
  Set f = Feuil28
    For i = 8 To 11
    nom = f.Cells(i, 1)
    tb = Split(nom)
    nb = UBound(tb)
    ad1 = ""
    For J = 0 To nb
    ad1 = ad1 & tb(J) & "_"
    Next J
    ad1 = Left(ad1, Len(ad1) - 1)
    On Error Resume Next
    With f.Cells(i, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & ad1
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Sélection"
        .ErrorTitle = ""
        .InputMessage = "Choisissez une valeur"
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With
    On Error GoTo 0
    Next i
End Sub

Bonjour

Ton fichiers avec tes données confidentielles, je n'en ai pas besoin, c'est un fichier avec des données bidons qui produisent l'erreur

Mais un truc m'interpelle

Greg.leo a écrit :

Si je vous le met dans un autre fichier avec des données fictives le code fonctionne...

Cela veut dire que dans tes données réelles quelque chose le bloque

As tu vérifies ad1 à la sortie de la boucle ?

Bonjour Banzai64,

Je sais bien que que le fichier avec les données confidentiels n'est pas nécessaire. Néanmoins je n'arrive pas a recréer l'erreur dans un fichier avec des données fictives.

Mon fichier entre temps a évolué : Lorsque j'ai créé ce sujet ce dernier comportait 31 onglets et le code bloquait au 28 ème.

Aujourd'hui mon fichier comporte 36 onglets et le code bloque au 34ème onglet. Pourtant je n'ai absolument RIEN touché aux onglets 28/29/30/31... Pourquoi diable ce code fonctionne t'il maintenant pour ces onglets et pourquoi bloque t'il maintenant au 34ème onglet...

Lorsque vous dites : As tu vérifié ad1 à la sortie de la boucle ?

Comment puis je réaliser ceci ?

Merci pour votre aide.

Nouveau constat : j'ai copié l'onglet 34 ainsi que le code VBA cité dans le premier message dans un nouveau fichier Excel : le code fonctionne et n'indique aucune erreur...

Bonjour

Juste après la boucle insères le code suivant

    For J = 0 To nb
    ad1 = ad1 & tb(J) & "_"
    Next J
If B > 28 Then   ' notes le numéro - 5 de la feuille qui plante
      MsgBox "Ad1 :*" & ad1 & "*"
    End If
    ad1 = Left(ad1, Len(ad1) - 1)

Cela risque d'être long car il te faudra acquitter à chaque message

Sinon quand le plantage a lieu

Dans la fenêtre "Exécution" tu tapes ? ad1

Et tu fais "Entrée"

Autre solution

Lors du plantage, passes le curseur dessus la variable ad1

Bonjour,

J'ai effectué votre dernière solution : Autre solution, Lors du plantage, passes le curseur dessus la variable ad1

Excel m'indique : Ad1 = ""

J'ai aussi intégré votre code mais au lieu de mettre B>28 j'ai mis B>33 et les mots que la MsgBox m'affiche ne sont absolument pas ceux présent sur la Feuil34. En effet ces mots sont présents sur la Feuil7.

Pour information ( Je ne sais pas si c'est important) j'ai changé le nom des Feuil dans les propriétés pour pouvoir réaliser la boucle.

Par exemple la Feuil26 a été renommé en Feuil7. L'erreur peut elle provenir de là ? (Une autre feuil se nomme maintenant Feuil26)

Bonjour

Toujours aussi difficile sans support

A vérifier

Quand tu écris Set f = Worksheets(B) tu affectes à la variable f la feuille numéro B : Attention pas celle qui se nomme "9" ou "10" ou "28" mais la 9ème, 10ème ou 28ème feuille en partant de la gauche de ta fenêtre

Alors si B = 28 mais à la 28ème position il y a la page "Toto', alors les informations seront extraites de la page "Toto"

Bonjour Banzai64,

Merci pour votre patience : vous avez trouvé le problème ! Effectivement, "f" prend l'ordre des feuilles dans le classeur et non leur intitulé !!

Merci beaucoup pour votre aide qui m'a été très précieuse !!