Créer une feuille par macro et y intégrer une macro

[Anne]

Hello,

pb : je crée une feuille par macro mais je voudrais ajouter un évènement à cette nouvelle feuille créee dans Worksheet_Calculate.

Est-ce possible ?

Merci beaucoup

Anne

PS : j'ai fait une recherche et trouvé un post qui ressemble à mon problème (auquel il ne semblait pas y avoir de solution) mais je n'en suis pas sûre à 100%

 

[Ti]

oui, c'est faisable, en passant par l'objet VBIDE, activeVBProject (en déclarant au préalable la référence Microsoft Visual Basic For Application Extensibility 5.3).

 

[Anne]

Ouahou !!
Ca a l'air trop élaboré pour moi, je vais trouver un autre moyen.
Merci beaucoup quand même Ti.

Anne

 

[Ti]

Désolé de ne pas avoir mis de démo : pas le temps...

 

[Ti]

Bon, comme tu m'avais l'air assez désemparé, je t'ai fait une macro très simplifiée qui insère une nouvelle feuille dans le classeur et qui, dans la foulée, ajoute à cette feuille une macro Worksheet_Calculate

Sub InsertionLignesDeCode()
'Ti 16-01-03
'pour que cette macro fonctionne, il faut aller dans le menu
'Outils...Références de VBA et cocher la référence :
'"Microsoft Visual Basic For Application Extensibility 5.3"

Dim VBComp As VBComponent, WkBk As Workbook
Dim NomFeuille As String, NomCode As String
'le nom de la nouvelle feuille
NomFeuille = "Feuille Test"
Set WkBk = ThisWorkbook
With WkBk
'on l'insère en dernière position dans cet exemple
With .Worksheets.Add(after:=Sheets(.Worksheets.Count))
.Name = NomFeuille
End With
NomCode = .Worksheets(NomFeuille).CodeName
Set VBComp = .VBProject.VBComponents(NomCode)
End With

With VBComp.CodeModule
.InsertLines .CountOfLines + 1, "Private Sub Worksheet_Calculate()"
'ligne de test (à enlever bien sûr)
.InsertLines .CountOfLines + 1, " msgbox ""essai de Worksheet_calculate"""
'ici les autres lignes de la macro
'.InsertLines .CountOfLines+1, " "
'.InsertLines .CountOfLines+1, " "
.InsertLines .CountOfLines + 1, "end sub"
End With

End Sub

 

[Anne]

J'ai récupéré une solution qui marche :

pour éviter d'avoir trop à écrire, j'ai au préalable crée, dans un module, la fonction (qui s'appelle Recalcule_feuille()) que je veux voir s'éxécuter avec la procédure évènementielle Worksheet_Calculate() et je fais appeler cette fonction par le Worksheet_Calculate() que je crée ci-dessous :


Sub Macro_Qui_Cree_Macro_Nouvelle_Feuille()


Dim ws As Worksheet, s As String
Sheets.Add
ActiveSheet.Name = "Nouvelle Feuille"

Sheets("Nouvelle Feuille").Select

Set ws = ActiveSheet
s = "Private Sub Worksheet_Calculate()" & vbLf _
& "Recalcule_feuille" & vbLf _
& "End Sub"

ThisWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule.AddFromString (s)


End Sub

 

[Ti]

j'ai oublié de préciser : si tu fais du déboggage et que tu es en mode pas à pas durant l'insertion des lignes de code, tu risques un plantage d'Excel...

 

[Anne]

Ti, on vient juste de se croiser !!!

Merci pour ta solution : c'est extra et très gentil !

 

[Ti]

bon, je ne voulais pas utiliser AddFromString , mais je vois que tu l'as fait sans moi, ce qui est préférable à la série de InsertLines que je te proposais (c'est plus compact et plus rapide). 🙂

 

[Anne]

Moi, je n'ai rien créé du tout, ce sont encore de bonnes âmes comme toi qui m'ont refilé le truc...

Bon je vais faire un gros dodo et compter mes macros !

A+

Anne