Insérer (ou retirer) un tableau sous conditions

Bonjour,

Je suis actuellement entrain de créer une feuille excel avec une liste déroulante de choix. Si on choisi dans cette liste "Bio", alors j'aimerais que le tableau "Organic_Statement" s'affiche quand on exécute la macro. Pour cela, pas de soucis, j'ai réussi à le faire :

Sub CommandButton1_Click()

  Application.ScreenUpdating = False

'Bio or not

If Sheets("Masque").Range("K3").Value = "Bio" Then
         Sheets("Bio ou pas").Range("Organic_Statement").Copy
         Sheets("Masque").Range("A22").Insert

Else
End If

 Application.CutCopyMode = False
End Sub

Le soucis, c'est que pour peu que je me sois tromper, et que décide de relancer le script en mettant "non-bio" le tableau "Organic_Statement" sera toujours là. Logique me direz-vous, je ne demande nulle part de le supprimer. Le problème est que quand j'exécute cette macro, le tableau insérer ne porte plus le nom "Organic_Statement" mais "Organic_Statement**" (ou ** est un nombre). De plus, si on exécute deux fois la macro, on aura 2 tableaux, 3 fois 3 tableaux... Pas pratique !

Du coup, j'aimerais ajouter une condition du style :

If Sheets("Masque").Range("Organic_Statement**").TableExists = False

Le problème, c'est que, comme vous pouvez vous en douter, ça ne marche pas. Du coup, je viens vous demander votre aide. En gros, je voudrais avoir un code qui fait :

Si Bio et Tableau absent => l'insérer

Si Bio et Tableau présent => ne rien faire

Si Non bio et tableau présent => Le supprimer

Si non bio et tableau absent => ne rien faire.

En espérant que quelqu'un pourra m'éclairer, je vous remercie d'avance pour le temps que vous prendrez pour m'aider.

Bonjour,

Pas de modèle ! Et tes propos sont un peu brouillon... Tu ne précises pas si ton tableau est constitué par une plage normale nommée ou s'il s'agit d'un tableau Excel !

Le fait qu'il se renomme automatiquement à la copie fait pencher vers ce dernier cas, mais ce serait mieux de confirmer.

Donc pour ta première question, il te suffit de tester si A22 est vide avant d'insérer :

        With Sheets("Masque").Range("A22")
            If Value = "" Then .Insert
        End With

Pour la seconde, le nom est mis automatiquement. Tu peux le modifier : propriété ListObject.Name, mais RExcel t'interdiras de donner un nom existant à un tableau, il faudra donc en choisir un autre si tu veux le changer.

Cordialement.

edit : et tu peux supprimer ton CutCopyMode = False, strictement inutile, ta copie ne se faisant pas sur la feuille active...

Merci pour ta réponse

Pour les précisions demandées :

Il s'agit bien d'un tableau, pas d'une plage nommée.

Pour ta solution, j'y avais pensé mais le fichier est soumis à modification. Donc, pour peu qu'on ajoute une ligne avant la 22, la macro deviendra fausse, ce qui ne m'arrange pas.

Pour la seconde, le nom est mis automatiquement. Tu peux le modifier : propriété ListObject.Name, mais RExcel t'interdiras de donner un nom existant à un tableau, il faudra donc en choisir un autre si tu veux le changer.

Mais si je fais ça, c'est après avoir insérer le tableau, non ? Du coup, imaginons j'insère le tableau, je le supprime puis je veux le ré-insérer. Il n'aura plus le même nom, si ?

Encore merdi pour l'aide

Clarifions : ton Tableau est sur "Bio ou pas", il a un nom. Tu le copies sur "Masque" : il ne peut avoir le même nom ! Excel le nomme donc à partir du nom antérieur en ajoutant un numéro d'ordre.

Si tu veux avoir un nom prédéfini, sachant que tu ne peux avoir le même, il faut en choisir un autre, et modifier après insertion le nom mis par Excel. C'est un objet ListObject que tu nommes, non un objet Range...

Par ailleurs il te sera loisible à ce moment-là de tester l'existence du nom avant insertion pour éviter des insertions multiples...

Cordialement.

Clarifions : ton Tableau est sur "Bio ou pas", il a un nom. Tu le copies sur "Masque" : il ne peut avoir le même nom ! Excel le nomme donc à partir du nom antérieur en ajoutant un numéro d'ordre.

Jusqu'ici je suis d'accord et je comprend

Si tu veux avoir un nom prédéfini, sachant que tu ne peux avoir le même, il faut en choisir un autre, et modifier après insertion le nom mis par Excel.

Je suis d'accord jusque là. Le problème, c'est comment je fais pour renommer que ce tableau là, sachant qu'il y aura d'autres choix (que bio ou pas) qui ameneront aussi des nouveaux tableaux ? Je dois partir vers quelque chose comme :

If Sheets("Masque").Range("K3").Value = "Bio" Then
      Sheets("Bio ou pas").ListObject("Organic_Statement").Copy
      Sheets("Masque").ListObject("A22").Insert
      Sheets("Masque").ListObject("A22").Name = MonNom

Encore merci pour ton aide... Je suis débutant en VBA, c'est un peu compliqué pour moi...

Rudiments de programmation VBA :

objet1 : Sheets("Masque")

on veut renvoyer un objet ListObject : propriété ListObjects de l'objet Worksheet, qui renvoie la collection des objets ListObject de la feuille

Pour n'en renvoyer qu'un, on utilise la propriété Item avec le nom de l'objet ou son numéro d'index ("Item" n'apparaît pas mais est toujours implicite...)

S'il n'y a qu'un tableau sur la feuille :

Sheets("Masque").ListObjects(1) renverra le tableau, on peut donc faire :

Sheets("Masque").ListObjects(1).DisplayName = "NouveauNom"

Pour faire quelque chose de cohérent, il faut penser complètement ta configuration de départ et ce que tu veux obtenir et envisager tous les cas de passage... Sans quoi tu te heurteras toujours à des imprévus !

J'ai essayé :

If Sheets("Masque").Range("K3").Value = "Bio" Then
      Sheets("Bio ou pas").Range("Organic_Statement").Copy
      Sheets("Masque").Range("A22").Insert
      Sheets("Masque").ListObjects("Organic_Statement2").DisplayName = "NouveauNom"
Else
    Sheets("Masque").Range("NouveauNom[#All]").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Selection.Delete Shift:=xlUp
End If

Le problème, c'est que si je met bio en K3 => j'ai bien le tableau sous le nom de NouveauNom.

Si je met non bio après, je supprime bien le tableau.

Si maintenant, je remet derrière bio, le tableau collé se nomme "Organic_Statement3" et donc il ne se renomme pas. Le problème, c'est que je vais le faire avec un grand nombre de tableaux, donc je ne peux pas vraiment utiliser les numéros.

Pour la condition, j'ai testé :

If Sheets("Masque").Range("K3").Value = "Bio" AND Sheets("Masque").ListObjects("NouveauNom").Exists = False Then
...

Mais ça ne marche pas :/

En tout cas, encore un grand merci, j'avance bien et ça se rapproche de ce que j'ai en tête !

Si tu ne respecte pas la syntaxe VBA en ton code, cela ne peut fonctionner !

Et si tu inventes des propriété dont les objets invoqués ne disposent pas, non plus !

J'avais utilisé "NouveauNom" parce qu'il ne m'appartient pas de définir un nom à ta place...

"Organic_Statement2" => Qu'est-ce qui te permet d'affirmer qu'Excel donnera ce nom ? Il le donnera peut-être une fois, mais en tout état de cause, ce n'est pas sûr, et ce ne sera jamais qu'une fois !

"NouveauNom[#All]" => As-tu ce nom dans le classeur référant à une plage ?

Sélectionner est opération parasite à proscrire.

Et ton CutCopyMode à cet endroit est carrément absurde !

Et il faut éviter de suivre bêtement l'enregistreur ! (il serait mieux de ne pas l'utiliser d'ailleurs).

Bonjour,

Si tu ne respecte pas la syntaxe VBA en ton code, cela ne peut fonctionner !

Et si tu inventes des propriété dont les objets invoqués ne disposent pas, non plus !

Je suppose que tu parles de la condition. Je ne comprend pas ta remarque, If AND then c'est possible, non ? La propriété exists n'existe pas ? (lol) Ou bien tu veux dire que vu que l'objet n'existe pas, il ne peut pas vérifier si exists=false ?

J'avais utilisé "NouveauNom" parce qu'il ne m'appartient pas de définir un nom à ta place...

Je me doute bien, mais pour le moment le nom m'importe peu et c'est plus clair ainsi, non ?

"Organic_Statement2" => Qu'est-ce qui te permet d'affirmer qu'Excel donnera ce nom ? Il le donnera peut-être une fois, mais en tout état de cause, ce n'est pas sûr, et ce ne sera jamais qu'une fois !

Effectivement, tout le problème est là : il ne le donne que la première fois, donc ça permet de bien l'afficher et de le supprimer en cas d'erreur, mais après pas. J'aimerais lui imposer le nom au moment de l'insertion, mais sachant que j'aurai beaucoup de tableaux sur la feuille (présent ou absent en fonction des choix), je ne sais pas trop comment faire vu que, comme tu l'as bien dit, je ne sais pas le nom qu'excel donnera.

"NouveauNom[#All]" => As-tu ce nom dans le classeur référant à une plage ?

Dans le cas où j'ouvre le fichier et j'éxecute la macro oui car le copier-coller s'appelle bel et bien "Organic_Statement2" donc il se renomme bien.

Sélectionner est opération parasite à proscrire.

Et ton CutCopyMode à cet endroit est carrément absurde !

Et il faut éviter de suivre bêtement l'enregistreur ! (il serait mieux de ne pas l'utiliser d'ailleurs).

Effectivement, je pense plutot partir sur :

Sheets("Masque").Range("NouveauNom[#All]").Delete Shift:=xlUp

J'avais essayé ça à la base (sans le shift) donc ça ne remontait pas. C'est pour ça que j'étais passé par l'enregistreur.

Encore merci pour ton aide

Cordialement

Bonjour,

Décidément ! Il y a encore beaucoup à faire !

VBA est un type de programmation dit "orienté objet" conçu pour être utilisé avec les applications Office (et quelques autres) qui fusionne deux composants distincts : les outils qu'offre Visual Basic, langage de programmation, et la bibliothèque d'objets de l'application, au cas particulier Excel, que tu peux donc utiliser directement en VBA sans avoir à en générer des instances.

Pour se servir correctement de VBA, il y a donc lieu d'avoir un minimum de connaissance des instructions VB d'un côté, et de l'autre du modèle d'objet Excel, soit de la hiérarchie des composants Excel érigés en objets...

Et comme il est difficile de tout savoir sur tout ! on dispose notamment d'une Aide qu'il est indispensable de consulter très souvent pour vérifier tel ou tel point de syntaxe...

Je dirais volontiers que la différence essentielle entre un débutant et un non-débutant est que le premier ne pense pas à consulter l'Aide ou vérifier ce qu'il écrit, contrairement au second !

Tout objet (dans le cas présent il s'agit d'objets Excel) possède des propriétés et des méthodes. Les propriétés sont des caractéristiques de l'objet, dont certaines permettent de renvoyer des objets enfants (rattachés hiérarchiquement à l'objet initial dans le modèle d'objet) en tant que collection, dont la propriété Item (de l'objet collection) permet alors de ne cibler qu'un seul membre. Les méthodes elles permettent d'agir sur l'objet... Quand on code on passe donc son temps à invoquer des objets, en partant de l'objet parent initial : Application (dont la propriété Application renvoie l'application Excel en cours d'exécution), et utilisant ses propriétés pour renvoyer des objets enfants, en définir ou recueillir des caractérisques, ou ses méthodes pour agir...

Lorsqu'on renvoie un objet, pour poursuivre : point suivi d'un mot-clé, ce mot-clé doit obligatoirement être une propriété ou méthode de l'objet pour que ça fonctionne ! Pour le vérifier rien de plus simple, on ouvre l'Aide, on tape le nom de l'objet. Pour un certain nombre, on va tomber tout de suite sur l'article : NomObjet.Membres de l'objet, sinon on ouvre un des articles dévolus à une propriété ou méthode de l'objet, on va à la fin trouver le renvoi vers : Membres de l'objet. On y trouvera la liste des Méthodes, Propriétés (et le cas échéant Evènements) de l'objet.

Et si ce qu'on voulait mettre après le point ne figure pas, inutile d'insister ! On cherche ce qu'il faudrait mettre pour aboutir, et si on ne trouve rien, c'est que peut-être il faudrait s'y prendre autrement.

Revenons à ton cas particulier : tu invoques un objet ListObject. Si tu tapes dans l'Aide ListObject, tu vas arriver à la liste des méthodes et propriétés de l'objet, tu la parcours et tu constateras que Exists n'y figure nullement !

Tu peux insister et taper Exists, cela te renverra à un article qui te montrera que Exists est bien une méthode d'objet mais de l'objet Dictionary ! On est loin du compte...

Quant à ton AND, j'aimerais te faire remarquer que lorsque tu tapes AND ou and, tu trouveras ensuite And dans le code remis en forme par VBA. C'est pourquoi il est important lorsqu'on soumet un problème en citant du code de prélever le code par copie dans le module pour le coller dans le post de façon que sa mise en forme qui fournit des indications non négligeables soit conservée.

Une de tes questions est de détecter un objet ListObject dont le nom aura été modifié automatiquement par Excel. Dans la mesure où tu dis avoir possiblement plusieurs tableaux de l'espèce dans ta feuille, sans trop avoir à réfléchir, tu devrais assez vite déterminer que pour trouver le bon tableau s'il existe, il serait judicieux de parcourir les objets ListObjects de la feuille, ce qui t'amène à utiliser une variable objet lo (par exemple) déclarée en tant que ListObject, pour parcourir la collection au moyen d'une boucle ForEach... Next, sachant que lorsque tu l'auras trouvé, ta variable lo représentera l'objet et tu pourras l'utiliser pour renommer ! (quoi de plus simple alors!!! )

Le tableau copié avait un nom : "Organic_Statement". Excel y aura ajouté quelque chose, tu as donc à chercher un nom qui réponde au modèle : "Organic_Statement*". La comparaison des noms existants (des ListObjects) avec ce modèle s'effectue avec l'opérateur Like.

Si dans le nouveau nom tu gardes le nom initial avec un ajout (autre que ceux que mettra Excel) tu le détecteras de la même façon avec le modèle. Soit tu cherches avant d'insérer, et tu n'insères que si tu ne trouves pas, soit tu cherches pour renommer après insertion, et tu renommes si tu trouves...

Autre point : "NouveauNom[#All]" ! Ça tu le mets peut-être dans une formule Excel que tu tapes dans une cellule ! Tu peux le mettre aussi en construisant ta formule en références structurées en VBA... mais si tu émets une instruction VBA, telle :

....Range("NouveauNom[#All]")....

Cela présuppose que "NouveauNom[#All]" est un nom dans le classeur, référant à une plage, que tu vas pouvoir trouver dans le Gestionnaire de noms ! Et si tu ne l'y trouve pas, textuellement écrit comme tu l'as fait, c'est qu'il n'y a pas de plage nommée ainsi et l'expression ne pourra renvoyer aucun objet.

Je pensais l'avoir exprimé clairement !

Derniers éléments : quand on apprend à coder en VBA normalement, on ne pense même pas à sélectionner, et on progresse plus vite, et on produit un code d'emblée plus efficace. Quand on se drogue à l'enregistreur, pour s'en sortir (et arriver à produire du code qui fonctionne sans toujours avoir à demander, peut-être faut-il apprendre en plus comment fonctionne l'enregistreur et comment épurer ce code produit par lui. Et une fois qu'on a bien compris, on devrait pouvoir écrire sans plus penser à l'enregistreur.

Application.CutCopyMode = False est une sorte de tarte à la crème en la matière. La majorité de ceux qui le mentionnent (ou le laissent dans du code enregistré) ne savent pas ce que réalise cette commande. Et quand ils le savent et le laissent c'est qu'ils n'ont pas compris comment fonctionnent l'enregistreur.

L'enregistreur ne fait qu'enregistrer des manipulations (d'où l'abondance de Select, Activate... dans la mesure où en manuel on ne peut procéder qu'ainsi), s'il insère donc la commande ci-dessus, c'est qu'une des manipulations a abouti à vider le presse-papier de Windows.

Mais si une manipulation l'a fait (et a été enregistrée), elle le refera lors d'une nouvelle exécution, donc inutile de l'écrire. L'on assiste à ce paradoxe que si l'enregistreur écrit la commande, c'est qu'on peut l'effacer car elle s'accomplira naturellement, par contre si l'enregistreur ne l'écrit pas (et qu'on veuille vider le presse-papier), là il faudrait la mettre car on sait alors qu'elle ne se réalisera pas spontanément !

....Delete Shift:=xlUp : voilà un autre élément qui dénote du code enregistré, le passage d'arguments par noms, que l'enregistreur utilise exclusivement, alors que le passage normal et habituel se fait par position (sans nommer les arguments), ce qui produit un code plus concis et plus rapide à écrire ! (En outre, l'enregistreur répète tous les arguments qu'ils aient été laissés ou non à leur valeur par défaut, ce qui conduit à fournir des paramètres déjà paramétrés ! Répétition inutile et donc perte de temps !).

Autre point plus ténu : les constantes Excel en VBA ont été redéfinies dans les nouvelles versions en vue de les compléter, mais sans supprimer les anciennes pour compatibilité... mais l'enregistreur n'a pas été aligné sur les nouvelles constantes dans pas mal de cas. C'est pourquoi on trouve ici xlUp au lieu de xlShiftUp.

On pourrait ajouter des pages sur ce thème ! Je n'en ajouterais que deux : lorsqu'on copie pour coller en valeurs (ce qui ne concerne pas ton cas) on peut généralement toujours procéder sans copier-coller, méthode irréalisable en manuel mais qui sera plus rapide en VBA ; lorsqu'on trie, la méthode Range.Sort est toujours plus facile à écrire, plus courte, et aussi efficace que la méthode de définition du tri Worksheet.Sort qui est seule utilisée par l'enregistreur et dont la définition plus complexe, utilisant une floppée d'objets fait que les utilisateurs de code enregistré n'y touchent pas, ne sauraient pas de toute façon comment l'épurer, et ignorent que l'on peut faire autrement...

Sur ce, cordialement !

Hello !

Merci pour toutes ces infos

Décidément ! Il y a encore beaucoup à faire !

Une de tes questions est de détecter un objet ListObject dont le nom aura été modifié automatiquement par Excel. Dans la mesure où tu dis avoir possiblement plusieurs tableaux de l'espèce dans ta feuille, sans trop avoir à réfléchir, tu devrais assez vite déterminer que pour trouver le bon tableau s'il existe, il serait judicieux de parcourir les objets ListObjects de la feuille, ce qui t'amène à utiliser une variable objet lo (par exemple) déclarée en tant que ListObject, pour parcourir la collection au moyen d'une boucle ForEach... Next, sachant que lorsque tu l'auras trouvé, ta variable lo représentera l'objet et tu pourras l'utiliser pour renommer ! (quoi de plus simple alors!!! )

Du coup, je dois partir sur un truc du style :

Dim lo As ListObjects
ForEach Io in Sheets("Masque")
If Sheets("Masque").lo=ListObjects("NouveauNom") And K4 = bio (raccourci ici) then
...
Next

Je ne comprend pas comment renommer le tableau avant de le coller (comment vérifier s'il existe si je ne sais pas son nom ?) et la condition bon... compliqué tout ça !

Si déjà tu changes le nom de la variable après l'avoir déclarée, ça va pas aller loin (un peu de soin de l'écriture tout de même ! )

Et ta syntaxe n'est toujours pas bonne !

Dim lo As ListObject  'si tu mets ListObjects c'est un objet-collection et non un object de la collection....!
For Each lo In Sheets("Masque").ListObjects  'la collection à parcourir c'est ListObjects et non Sheets !!
     If lo.Name Like "NomTablo*" Then  'dans la boucle, lo est un objet de la collection et se suffit pour le représenter
                    ' et l'opération consiste à comparer son nom...
                    ' Je laisse de côté l'autre condition... car il y a en fait un autre problème

J'ai repris ton post de départ où tu écris ceci :

Sheets("Bio ou pas").Range("Organic_Statement").Copy

Tu copies une plage nommée, la référence à la feuille n'est plus à faire dans ce cas (mais c'est une autre question...) Si tu dis que ta plage nommée est un Tableau Excel et on est parti sur cette hypothèse et le fait que ton tableau se renommait automatiquement à l'insertion... mais cela ne va pas avec la ligne ci-dessus !

Si "Organic_Statement" est le nom de ton tableau, Excel insère automatiquement un nom dans le Gestionnaire de noms. Mais ce nom couvre la zone de données du Tableau en excluant l'en-tête... !

....ListObjects("Organic_Statement") c'est bien le tableau en entier, mais

....Range("Organic_Statement") non, ce n'est qu'une partie, les données sans la ligne d'entête !

Si tu copies et insères le tableau entier, Excel introduiras un nom dans le Gestionnaire pour le nouveau tableau, mais si tu copies et insères la plage de données, il ne le fera pas !

Avec les éléments qui ont été exposés, cela ne peut donc pas marcher comme tu dis, ou bien il manque des éléments.

Il convient donc de reprendre tout au départ avec la véritable configuration...

Cordialement.

Bonjour,

Merci pour ta réponse. J'en arrive à quelque chose comme ça :

'Bio or not
Dim lo As Object
For Each lo In ListObjects
    If lo.Name Like "Organic_Statement*" Then
       lo.ListObjects("Organic_Statement*").Delete Shift:=xlUp
    Else
    End If
Next
If Sheets("Masque").Range("K3").Value = "Bio" Then
   Sheets("Bio ou pas").Range("Organic_Statement").Copy
   Sheets("Masque").Range("A22").Insert
Else
End If

End Sub

La phrase pour supprimer pose problème. Je ne comprend pas comment dire "S'il existe, supprime le" parce que le lo.Name Like ne marche pas non plus :/ Mais je sent qu'on est proche, non ?

Si "Organic_Statement" est le nom de ton tableau, Excel insère automatiquement un nom dans le Gestionnaire de noms. Mais ce nom couvre la zone de données du Tableau en excluant l'en-tête... !

....ListObjects("Organic_Statement") c'est bien le tableau en entier, mais

....Range("Organic_Statement") non, ce n'est qu'une partie, les données sans la ligne d'entête !

Pourtant, même avec un code simple (juste copier insérer) si je met "ListObjects" à la place de Range ça ne marche plus.

Alors que, si je sélectionne mon tableau (cfr pièce jointe) il porte bien le nom

Pour info, j'ai réussi à réousre mon problème ! (avec un peu de détermination

Dim objList As ListObject
For Each objList In ActiveSheet.ListObjects
    objList.DataBodyRange.Select
    Selection.ClearContents
    Selection.Delete Shift:=xlUp
Next objList

'Bio or not
If Sheets("Masque").Range("K3").Value = "Bio" Then
   Sheets("Bio ou pas").ListObjects("Organic_Statement").DataBodyRange.Copy
   Sheets("Masque").Range("A22").Insert
Else
End If

Je passe en résolu. Si tu passes par ici, merci pour ton aide