[VBA] Générer automatiquement un code VBA dans une nouvelle feuille

Bonjour,

Malgré les explications déjà faites sur ce forum, notamment dans ces discussions :

https://forum.excel-pratique.com/viewtopic.php?t=53841

https://forum.excel-pratique.com/viewtopic.php?f=2&t=132483

Je ne parviens pas à faire fonctionner ce code :

Sub CreateWsEvenMacro()
Dim a As Integer, b As Integer

'ActiveWorkbook.VBProject.VBComponents.Count

    With ActiveWorkbook(nm & "_" & prn).codeModule
    a = .countOfLines
        .insertLines a + 1, "Private Sub Worksheet_activate()"
        .insertLines a + 2, "Call Decharge_Usf"
        .insertLines a + 3, "Bandeau_actions.Show 0"
        .insertLines a + 4, "End Sub"
    End With
End Sub

Je n'ai pas d'erreur, il ne se passe juste rien car je pense qu'Excel ne trouve pas ce que je cherche.

Le problème vient d'ici :

ActiveWorkbook(nm & "_" & prn).codeModule

Que j'ai également essayé d'écrire :

ActiveWorkbook.VBProject.VBComponents(nm & "_" & prn).codeModule

Ou bien

ActiveWorkbook.VBProject.(nm & "_" & prn).codeModule

Savez-vous ce que je dois écrire pour qu'Excel trouve, dans le document, le nom de la feuille recherchée ?

Cela ne fonctionne pas non plus si je demande à ce qu'Excel génère une feuille toujours nommée "Feuil1", sur laquelle je lancerais la macro ci-après, que je renommerais après.

Mon document est tout imbriqué, alors pour extraire la partie du code qui m'intéresse et proposer un document pour l'exemple ça va me prendre un peu de temps, je vous le transmets dès que possible !

Merci de votre attention !

Bonne soirée

Bonsoir,

d'où vient la feuille sur laquelle vous voulez écrire ce code ?

C'est un ajout de feuille ? Une copie de feuille ?

@ bientôt

LouReeD

Bonjour, bonjour LouReeD,

nm et prn ne sont pas définis dans ta macro !

de plus (mais je pense que ce n'est qu'un exemple ...) si nm = "Ti"et prn = "ta" alors le nom de la feuille ne fait pas "ti_ta" mais "Ti_ta" !

Tu devrais aussi savoir qu'il vaut mieux poster un fichier ...

Attention, ne ps confondre codename et nom de la feuille !!

Une solution qui fonctionne ...

Sub CreateWsEvenMacro()
Dim a As Integer, b As Integer
Dim nm As String, prn As String

nm = "Ti"
prn = "ta"

'ActiveWorkbook.VBProject.VBComponents.Count

    With ActiveWorkbook.VBProject.VBComponents(WsCodeName(Sheets(nm & "_" & prn))).codeModule
    a = .countOfLines
        .insertLines a + 1, "Private Sub Worksheet_activate()"
        .insertLines a + 2, "' essai"
        .insertLines a + 3, "MsgBox ""Bienvenue !"""
        .insertLines a + 4, "End Sub"
    End With
End Sub

Function WsCodeName$(Ws As Object)
On Error Resume Next
With Application.VBE.MainWindow
     WsCodeName = Ws.CodeName
End With
End Function

Il faut aussi approuver l'accès au modèle d'objet du projet VBA

Bonjour,

Merci beaucoup pour toutes vos réponses !

L'ajout de feuille se fait via une macro (dans un UF) où j'indique des élements qui vont constituer le nouveau nom de la feuille.

J'ai un bandeau (un autre UF) qui doit s'ouvrir et se mettre à jour en fonction du type de feuille créé, et pour que ce dernier soit activé, j'ai besoin d'écrire dans chaque nouvelle feuille créée le code qui va l'ouvrir (

"Bandeau_actions.Show 0"

). Mais je n'arrivais pas à désigner la nouvelle feuille créée.

Attention, ne ps confondre codename et nom de la feuille !!

C'était exactement mon problème, je n'arrivais pas à trouver comment désigner le "codename".

Dans le code que vous avez proposé, c'est ici que j'ai pu voir ce qui manquait :

With ActiveWorkbook.VBProject.VBComponents(WsCodeName(Sheets(nm & "_" & prn))).codeModule

>

WsCodeName

J'ai aussi ajouté la fonction dans ma macro, mais sans bien comprendre son rôle.

Le résultat correspond bien à ce que j'espérais obtenir !

Merci beaucoup

Le Drosophile a écrit :

J'ai aussi ajouté la fonction dans ma macro, mais sans bien comprendre son rôle.

Au début, moi non plus, rassure-toi...

Cela évite justement de confondre nom de feuille et code de la feuille, le on error resume next contourne cette difficulté.

Function WsCodeName$(Ws As Object)
On Error Resume Next
With Application.VBE.MainWindow
     WsCodeName = Ws.CodeName
End With
End Function

Bref une belle recette simple que j'avais un jour dénichée pour justement résoudre ce sujet de nommage des feuilles !

edit : explication farfelue ... voir la fin du topic

Bonjour,

si je comprend bien :

ActiveWorkbook.VBProject.VBComponents

attend en paramètre le CodeName VBa de la feuille

Du coup votre fonction indique que le CodeName est égale au Name de la feuille, du coup tout est bon !

Est-ce bien cela ?

@ bientôt

LouReeD

parfois oui, parfois non, mais plutôt non ! c'est le piège ...

un exemple ...

Si dans le code au lieu d'écrire :

With ActiveWorkbook.VBProject.VBComponents(WsCodeName(Sheets(nm & "_" & prn))).codeModule

avec votre fonction, on écrit :

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn)).codeModule

Cela marche-t-il ?

@ bientôt

LouReeD

Bonjour,

Si dans le code au lieu d'écrire :

With ActiveWorkbook.VBProject.VBComponents(WsCodeName(Sheets(nm & "_" & prn))).codeModule

avec votre fonction, on écrit :

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn)).codeModule

Cela marche-t-il ?

Non, dans ce cas là, le "CodeName" n'est pas trouvé et la macro saute la partie "with..."'

LouReeD a écrit :

avec votre fonction, on écrit :

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn)).codeModule

Cela marche-t-il ?

Non ! En effet, car nm & "_" & prn est le nom de la feuille, et pas forcément le nom du codemodule; pour cela il aurait aussi fallu donner ce nom en passant par les propriétés de la feuille comme dans l'exemple donné (nom sans espace alors que le nom apparent de l'onglet peut en avoir).

Le pire qu'on puisse rencontrer c'est ceci : le codemodule est Feuil3 alors que la feuille que je viens d'ajouter a pour nom par défaut Feuil2

Pour le fun ...

Voilà la réponse à ma question ...

Je veux dire par là, que comme Drosophile, je me demandais à quoi servait la fonction puis que "codename=codename" en fin de compte.

Votre code modifié ainsi fonctionne aussi :

Sub test()
Dim sh As Worksheet
For Each sh In Worksheets
    sh.Cells(1, 1) = "WsCodeName"
    sh.Cells(1, 2) = sh.CodeName
    sh.Cells(2, 1) = "Name"
    sh.Cells(2, 2) = sh.Name
Next
End Sub

Donc :

J'ai aussi ajouté la fonction dans ma macro, mais sans bien comprendre son rôle.

et

Au début, moi non plus, rassure-toi...

Cela évite justement de confondre nom de feuille et code de la feuille, le on error resume next contourne cette difficulté.

Voyez vous j'ai du mal à suivre...

On a un objet "SH" avec deux propriétés "Name" et "CodeName", je ne vois pas l'utilité de la fonction...

Bref, je n'ai pas votre niveau "grand frère" !

Donc je le prend comme c'est et puis je ne me pose plus de question sur le sujet...

@ bientôt

LouReeD

Merci d'avoir posé des questions ... cela permet de progresser. Moi non plus comme je le disais je n'avais pas tout à fait compris.

Cette fonction WsCodeName ne sert à rien en fait ! jusqu'à preuve du contraire car quelqu'un l'a bien pondue.

Il suffisait donc de remplacer

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn)).codeModule

par

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn).CodeName).codeModule

De même que dans mon exemple qui lui montre bien la différence entre CodeName et Name de le feuille ...

LouReeD a proposé un solution simple et logique.

En effet, écrit comme ça, la macro s'exécute également comme il faut !

Il suffisait donc de remplacer

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn)).codeModule

par

With ActiveWorkbook.VBProject.VBComponents(Sheets(nm & "_" & prn).CodeName).codeModule

Je reviens vers vous car en réalité cela ne marche pas. J'ai du me tromper dans mes tests.

Dans mon cas, si j'utilise cette ligne de code, alors rien ne se passe. En revanche, si j'utilise la fonction, là tout s'exécute comme souhaité.

Bonne soirée !

Comme quoi l'informatique...

@ bientôt

LouReeD

Mais si on regarde le fichier ci :

Alors? Est-ce que ça marche ?

@ bientôt

LouReeD

LOL ... MDR

La fonction est donc bien nécessaire ...

Option Explicit

Sub Test_LouReeD()
    Dim a, Un As String, Deux As String
    Un = "Lou"
    Deux = "ReeD"
    ' on crée une feuille
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = Un & "_" & Deux
    ' on cible le codeModule de la feuille créée par son CodeName
    With ActiveWorkbook.VBProject.VBComponents(WsCodeName(Sheets(ActiveSheet.Name))).codeModule
        ' on inscrit les lignes de code VBA dans la feuille créée
        a = .countOfLines
        .insertLines a + 1, "Private Sub Worksheet_activate()"
        .insertLines a + 2, "' ceci est une remarque de LouReeD"
        .insertLines a + 3, "MsgBox(""Bonsoir"")"
        .insertLines a + 4, "End Sub"
    End With
End Sub

Function WsCodeName$(Ws As Object)
On Error Resume Next
With Application.VBE.MainWindow
     WsCodeName = Ws.CodeName
End With
End Function

Et bien c'est à rien y comprendre car ça marche chez moi sur mon fichier d'origine et ce sans fonction...

A croire que le téléchargement ou le passage sur vos machine le fait boguer !

Faut que je retourne sur mon fichier pour voir ça !

@ bientôt

LouReeD