Insertion formule VBA

Bonjour,

Je souhaiterai insérer cette formule via VBA dans une liste déroulante :

=DECALER(INDIRECT("DONNEES[Liste des tâches]");0;0;$D$6)

Si je réalise cette opération manuellement cela fonctionne. Cependant via Visual Basic cela ne fonctionne pas. Ce message s'affiche sans même lancer le programme lorsque j'insère ma formule dans mn code :

Erreur de compilation: Attendu: Fin d'instruction

Voici mon code :

Range(Cells(Premiereligne, 34), Cells(derniereLigne, 34)).Select

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=DECALER(INDIRECT("DONNEES[Liste des tâches]");0;0;$D$6)"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

PS : hier ça fonctionnait et aujourd'hui en rallumant excel cela ne fonctionne plus ! J'avais aussi tester cela :

=DECALER(INDIRECT(""DONNEES[Liste des tâches]"");0;0;$D$6)

Bonjour,

C'est la difficulté d'écrire des guillemets dans une formule, alors que c'est un caractère qui délimite du texte pour VBA...

Essaye ceci :

"=DECALER(INDIRECT(" & Chr(34) & "DONNEES[Liste des tâches]" & Chr(34) & ");0;0;$D$6)"

Merci pour ton interêt, le code n'a malheureusement pas fonctionné. J'ai Erreur de syntaxe maintenant.

J'ai réalisé un petit excel en exemple de mon problème :

Quand vous allez dans l'onglet "Données" puis "Validation des données" sur la case C2 vous pouvez voir que le code est inscrit manuellement et que ça fonctionne !!!

Cependant si vous ouvrez le VBA ci-joint, je lui demande de faire de même en F2 mais ça ne fonctionne pas !

C'est la formule DECALER qui semble poser problème. Mais au final, elle est parfaitement inutile lorsque l'on fait référence à un tableau structuré, dont le principal avantage est de se redimensionner automatiquement à l'ajout ou la suppression de données.

Voilà un code fonctionnel (sur votre fichier exemple) :

Sub Validation1()

With Range("F2").Validation
    .Delete
    .Add Type:=3, AlertStyle:=1, Formula1:="=INDIRECT(""DONNEES[Saisir les noms des opérateurs ]"")"
End With

End Sub

Alors oui et non. Tu pointes le problème mais ce n'est pas ce que je souhaite

La fonction décaler me sert ici à n'avoir dans ma liste déroulante QUE les valeurs pleines de ma colonne de personne. Lorsque j'utilise ta formule j'obtiens en effet une liste déroulante avec le nom des personnes mais aussi une multitude de case vide correspondant aux cases vide du tableau.

C'est pourquoi j'ai noté en dessous du tableau NBVAL(colonne A) pour avoir le nombre de case avec une personne dedans et ensuite je procède à un décalage pour ne sélectionner que les N premières colonnes ou il y a un nom !

Mais au moins on sait maintenant que la fonction DECALER pose problème au code VBA.

Voilà pourquoi j'utilise cette fonction. mais comme je l'ai dis précédemment ce code VBA fonctionnait hier mais plante ce matin à l'allumage ! FRUSTRANT !

J'ai bien compris, mais l'utilité d'un tableau structuré est bien d'éviter les lignes vides superflues... Donc en théorie, ton tableau avec 6 noms devrait s'arrêter à la ligne 7 pour avoir un intérêt.

Sinon, utilise seulement la fonction INDIRECT en faisant référence aux cellules non-vides.

Exemple :

=INDIRECT("A2:A"&NBVAL($A$1:$A$100))

DECALER est parfaitement utilisable sous VBA, mais pour une raison que j'ignore, son utilisation dans ta liste de validation plante.

Plus simple, on écrit pas de fonction Excel, mais directement le résultat évalué depuis VBA...

Sub Validation2()

Dim MonTxt As String

MonTxt = "=$A$2:$A$" & Application.CountA(Sheets("Feuil1").Range("$A$1:$A$100"))
With Range("F2").Validation
    .Delete
    .Add Type:=3, AlertStyle:=1, Formula1:=MonTxt
End With

End Sub

Alors en fait dans ma version réel je veux référencer dans ce tableau toute les personnes qui travaille sur le projet + les tâches à réaliser dans le projet + les identifiants des pièces sur lesquels on travaille + ...

J'ai envie que mon superviseur entre toutes les données d'entrée dans ce tableau pour créer un planning sur une autre page. (Là dessus je n'ai aucun problème). C'est donc normal que ma colonne comporte des vides car certaines colonnes sont plus fournies que d'autres. Dans ce planning il utilisera les listes déroulantes pour choisir les personnes en fonctions des tâches.

Donc il faut comprendre que mon tableau ne ressemble pas du tout à une seule colonne avec 3 noms à l'intérieur et que j'ai créé plein de listes déroulantes qui fonctionnaient à merveille hier. J'allume l'ordi : ça marche plus ! Pourquoi ? On ne sait pas, merci la VBA !

Pour réponse à ce sujet :

J'ai en réalité nommer la colonnes du tableau en inscrivant ladite formule dans l'espace pour la nommer.

Du coup dans ma VBA j'avais juste à faire apparaitre "=Le_nom_donné" est cela fonctionne !

Merci pour votre aide