Comment avoir une macro commune à mes classeurs?

Version imprimable

Voir 40 message(s) de cette discussion en une page

15/11/2007, 09h19
jay-hut

Comment avoir une macro commune à mes classeurs?

Bonjour,

Je suis débutant en VBA.
J'ai un grand nombre de fichiers excel où j'ai une macro qui est dupliqué afin de pouvoir faire une certaine action lorsque le classeur est modifié (Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)...
End Sub).
Mon problème est que lorsque je veux rajouter une fonctionnalité, je dois modifier l'ensemble des classeur.
Par conséquent, j'aimerais pouvoir faire une macro pour ces fichiers qui appellent l'ensemble des fonctions contenus dans un fichier (.bas?) afin de ne faire les modifications que sur ce fichier et qu'elles soient répercutées sur l'ensemble de mes classeurs.

Comment puis automatiser la lecture de la macro à l'ouverture d'un de mes classeur?

Merci par avance.

PS: Pour l'instant je n'ai réussi qu'à lire la macro d'un autre fichier .xls mais ce dernier doit être ouvert pour que cela fonctionne.
15/11/2007, 09h30
Pierre Fauconnier

Bonjour

Stocke ta macro dans le classeur perso.xls. Ce classeur se trouve dans le dossier XLSTART de l'utilisateur.

AstucePour le créer simplement, crée une macro (n'importe laquelle) avec l'enregistreur de macros et spécifie dans Enregistrer la macro dans que tu veux utiliser le classeur de macros personnelles.

Lorsque cela sera fait, ton classeur perso.xls sera disponible dans les projets VBA. Il sera automatiquement ouvert "invisible" à chaque session d'Excel.
15/11/2007, 09h36
ouskel'n'or

Tu as une solution : Placer ta macro dans un fichier que tu places dans "C:\Program Files\Microsoft Office\Office\XLOuvrir\" ou "C:\Program Files\Microsoft Office\Office10\XLStart" selon la version d'office que tu utilises et tu en masques les feuilles.
Par contre, la macro ne s'éxécutera pas à l'ouverture des "autres fichiers".Je te suggère donc de placer un bouton spécifique pour la lancer dans une barre d'outils existante ou à créer. Dès lors, ce bouton, et donc la macro sera accessible quelque soit le fichier ouvert.
Si en outre tu nommes ce fichier "Perso.xls", tout le monde saura de quoi tu parles et on pourra t'aider ;)

Edit
Grillé par Pierre
15/11/2007, 10h13
jay-hut

J'ai stocké mon fichier perso.xls dans "C:\Program Files\Microsoft Office\Office10\XLStart"
J'ai enregistrer la macro dans classeur de macros personnelles.
J'ai essayé avec une macro test et ça marche bien mais lorsque j'enregistre une macro avec à l'intérieur:
Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ... end sub

et que je modifie mon document alors là il ne prend pas en compte le changement dans la feuille et n'exécute pas le code de la macro automatiquement.

De plus le classeur n'est pas automatiquement ouvert "invisible" à chaque session d'Excel mais il est bien "visible".

N'est-il pas possible de faire une macro chargeant directement un fichier contenant le code vba afin que je puisse utiliser la fonction détectant un changement sur la feuille à l'ouverture de mes classeurs?
15/11/2007, 10h24
Pierre Fauconnier
En créant ton perso.xls comme je te l'ai renseigné, Excel ne le stocke pas dans program files... mais dans Documents and settings... et il est bien invisible à l'ouverture d'Excel.

Maintenant, il faut peut-être (probablement) adapter ta macro pour qu'elle fasse référence au classeur actif, car si tu utilises, par exemple
[code]Sub Macro1()
Debug.Print Worksheets("Feuil1").Parent.Name
End Sub
dans perso.xls, il te renverra ...perso.xls, alors que si tu utilises, toujours dans perso.xls, ceci
Code:

1 2 3 Sub Macro1() Debug.Print ActiveWorkbook.Worksheets("Feuil1").Parent.Name End Sub
tu auras le nom du classeur actif.

Ok?
15/11/2007, 10h32
ouskel'n'or

Pour perso.xls, j'ai précisé

Citation:

et tu en masques les feuilles.

Ainsi il sera transparent.
Pour l'événement "Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)", tu n'avais pas précisé que tu voulais utiliser celui-ci dans les classeurs ouverts. Sinon nous t'aurions dit que tu devais utiliser une autre méthode. Cet événement concerne le classeur dans lequel se trouve la macro, pas dans un autre.
A froid je ne vois qu'une manière de procéder mais peut-être y en a-t-il une autre :?
Consisterait à placer le code de ta macro dans le classeur en question par macro dans l'objet Thisworkbook de chaque classeur ouvert. (Cette macro pouvant être supprimée après usage...)
Tu dis mais à ta place j'attendrais de voir si quelqu'un n'aurait pas une autre proposition à te faire ;)
15/11/2007, 10h47
Pierre Fauconnier

Salut Ouskel'n'or...

Il n'y a pas moyen de masquer TOUTES les feuilles d'un classeur. Il en faut toujours au mois une visible...:cry:

En fait, lorsque ce classeur a été créé par Excel et qu'il est dans le bon dossier, il est ouvert en invisible par Excel.

Jay-hut,
Si j'étais toi, je créerais un classeur perso.xls comme je te l'ai proposé (outils/Macros/Nouvelle macro... et choisir classeur de macros personnelles et je copierais mon code dedans, pas un simple copier-coller en VBA.

Puis je m'attelerais à adapter la macro pour que, alors qu'elle est présente dans perso.xls, elle puisse fonctionner sur le classeur actif.

Il est bien plus simple, pour cela, de travailler avec des objets car tu pourras en manipuler les propriétés bien plus facilement...

Essaie de faire cela, puis reviens avec ta macro et on t'aidera à l'adapter...

Bon travail

Tout d'abord merci pour la rapidité de vos réponses.

J'ai crée un classeur et choisir classeur de macros personnelles, ça m'a bien crée les macro dans un classeur PERSO.XLS qui est ouvert dès que j'ouvre un document excel.
Lorsque j'execute une macro test de PERSO.XLS dans mon document excel, ça marche bien... et le classeur perso.xls reste bien invisible :).
Par exemple pour tester:
Code:

1 2 3 Sub Macro1() ActiveCell.FormulaR1C1 = "texte de perso.xls" End Sub
Donc quand j'exécute la macro, ça m'écrit bien dans la case active de la macro.

Maintenant j'aimerais pouvoir utiliser la macro prédéfinie lorsqu'un changement est détecté dans un classeur pour tous mes classeurs:
(on passe les cellules de la ligne de colonnes 2 à 5 en gris si on écrit GREY sur une cellule de la 1ère colonne)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If (Target.Columns.Count = 1 And Target.Rows.Count = 1) Then If (Target.Value = "GREY" And Target.Column = 1) Then ActiveSheet.Range(Cells(Target.Row, 2), Cells(Target.Row,5)).Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If End If End Sub
J'ai essayé de mettre la macro dans une nouvelle du classeur PERSO.XLS mais ça ne fonctionne pas.

Tu changes à nouveau les données par rapport à tes messages précédents.

Dans ceux-ci, tu souhaitais utiliser un bouton pour activer une macro.

Ici, tu veux utiliser un évènement pour l'ensemble du classeur

La solution est de déclarer un objet "excel.application" dans le perso.xls et de l'initialiser à l'ouverture de perso.xls, puis de gérer les évènements de cet objet excel application. Tu pourras gérer les évènements liés à l'application dans le module de classe ThisWorkbook de perso.xls

Le code suivant, dans le module ThisWorkbook de perso.xls permet cela
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Option Explicit Public WithEvents oApp As Excel.Application Private Sub oApp_SheetActivate(ByVal Sh As Object) MsgBox "Vous venez d'activer un onglet de " & Sh.Parent.Name End Sub Private Sub Workbook_Open() Set oApp = Excel.Application End Sub
Evidemment, comme les évènements d'application sont valables pour tous les classeurs, il faut, chaque fois que tu gères un évènement, tester quel était le classeur actif au moment de la survenance de l'évènement.

C'est un remaniement probablement assez conséquent de ton code...

15/11/2007, 11h22
Pierre Fauconnier

Une autre solution, moins contraignante, consisterait à créer un xla qui serait ouvert uniquement à l'ouverture des classeurs concernés, et qui serait fermé à la fermeture du dernier classeur concerné.

Dès lors, il conviendrait de transférer tout le code d'un des classeurs vers le xla, puis, dans chaque classeur concerné, de code l'ouverture du xla lors de l'ouverture du classeur, et sa fermeture à la fermeture du classeur, après vérification dans la collection des workbooks qu'il n'y a plus un seul classeur ouvert qui en a besoin...
15/11/2007, 11h33
ouskel'n'or

Citation:

Envoyé par Pierre Fauconnier

Salut Ouskel'n'or...

Il n'y a pas moyen de masquer TOUTES les feuilles d'un classeur. Il en faut toujours au mois une visible...:cry:

A ton intention, Pierre, Pièce jointe 21702... Méfie-toi ! :mouarf:
15/11/2007, 11h44
Pierre Fauconnier

Citation:

Envoyé par ouskel'n'or

A ton intention, Pierre, Pièce jointe 21702... Méfie-toi ! :mouarf:

Pas besoin de me méfier... Il faut juste que tu t'exprimes convenablement... :mrgreen:

Tu dis "Tu en masques les feuilles"... donc celui qui lit va... masquer les feuilles (format/Feuilles/masquer, ou en changeant la propriété Visible dans la fenêtre des propriétés des feuilles, en vba) pour chacune des feuilles de son classeur, et lorsqu'il voudra masquer la dernière, il aura un joli message d'Excel!

Ce que tu as fait (je suppose, car c'est ainsi que le perso.xls est géré), c'est que tu as utilisé un code du genre

Code:

thisworkbook.windows(1).visible = false

Me trompé-je?

Mais cela ne masque aucune feuille du classeur...

Bon, je sais, je suis chi... sur la précision des termes...

Bonne journée
15/11/2007, 11h46
jay-hut

Désolé c'est pour ça que j'ai précisé que j'étais débutant (je m'y suis mis hier ^^) et je dois faire pas mal de confusions.

A la base je pensais pouvoir faire une importation automatique d'un fichier contenant la macro (.bas) et utiliser la macro.
J'ai essayé manuellement et pour que la fonction Sub Workbook_SheetChange contenue dans le fichier .bas marche je dois faire:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call Module1.Workbook_SheetChange(Sh, Target)
End Sub

Puis la proposition de passer pas un classeur PERSO.XML m'allait bien, mais là j'ai du mal à comprendre ce que je dois déclarer dans PERSO.XML pour que l'événement Sub Workbook_SheetChange fonctionne sur les autres classeurs.

Enfin j'ai copier/coller la fonction
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) dans un fichier xla (j'ai enregistrer le classeur sous le format .xla puis ajouter à mes macros complémentaires).
Cependant l'événement n'est pas capturé... surement parce que je dois ouvrir le fichier xla avant.

Voilà en gros mon unique problème et le fait de pouvoir capturer un événement de changement dans une page pour mes classeurs mais d'avoir un code unique afin de pouvoir le faire évoluer. Mais je ne connais pas la meilleure méthode pour arriver à mes fins.
En espérant avoir été plus clair, encore merci pour vos aides.

En fait je pense que ce qui serait bien, ce serait de pouvoir faire une importation automatique d'un fichier .bas à l'ouverture du classeur, mais je ne vois pas comment on peut l'importer automatiquement.
15/11/2007, 12h19
ouskel'n'or

Il te reste la méthode que j'ai suggérée au début consistant à insérer le module contenant ta macro dans chaque fichier ouvert. SilkyRoad donne un exemple dans la FAQ
Pour ça, tu dois placer ton code dans un module, enregistrer le module (Clic droit sur son nom -> Exporter)
Import du module :
Tu places un bouton dans ta barre d'outils, bouton qui lance la macro d'importation.
Une fois le fichier ouvert, un clic sur le bouton placera le code dans Thisworkbook du fichier nommé par cette macro.
Si je reprends le code de Michel, à la place de

Citation:

Set Wb = Workbooks("NomClasseur.xls")

ce sera

Code:

Set Wb = ActiveWorkbook

Ensuite... tu nous dis ce que j'ai oublié d'expliquer...
15/11/2007, 12h20
Pierre Fauconnier

Citation:

Envoyé par jay-hut

En fait je pense que ce qui serait bien, ce serait de pouvoir faire une importation automatique d'un fichier .bas à l'ouverture du classeur, mais je ne vois pas comment on peut l'importer automatiquement.

Pour moi, devoir modifier le code par du code est toujours la plus mauvaise des solutions.

Les solutions proposées par Excel (classeur perso, classeurs XLA, ...) sont justement là pour éviter de devoir modifier du code "en live"...

Si tu as la main sur les classeurs, la solution de passer par un xla est LA solution adaptée à ton problème.

Si tu veux vraiment ajouter un module "en live", tu vas devoir passer par l'utilisation de vbcomponents, ... et, comme tu t'y es mis hier, je pense que ce n'est pas la meilleure approche pour démarrer avec VBA sur Excel, et cette solution, pour sympathique qu'elle puisse paraître, n'est à mon avis qu'un pis-aller...

Si tu souhaites utiliser la solution de Ouskel'n'or avec l'aide du tuto de Silkyroad, pense qu'il va falloir gérer le fait qu'il ne faut pas cliquer sur le bouton deux fois pour le même classeur...

[EDIT] Si, de plus, tu veux ajouter du code pour "jouer" avec des évènements sur tes feuilles, je te souhaite bon courage... N'oublie pas qu'en cas d'ajour par code, tu n'as pas droit à la moindre erreur dans ton code, car pas moyen de tester en debugage...[/EDIT]

Voici un code qui évite le problème que pourrait provoquer plusieurs clics sur ton bouton

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub EcrireDansThisWorkbook()
Dim i, nomFich, x As Long, LeCode(1 To 13)
    LeCode(1) = "Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)"
    LeCode(2) = "    If (Target.Columns.Count = 1 And Target.Rows.Count = 1) Then"
    LeCode(3) = "        If (Target.Value = ""GREY"") And Target.Column = 1 Then"
    LeCode(5) = "            ActiveSheet.Range(Cells(Target.Row, 2), Cells(Target.Row, 5)).Select"
    LeCode(6) = "            With Selection.Interior"
    LeCode(7) = "                .ColorIndex = 15"
    LeCode(8) = "                .Pattern = xlSolid"
    LeCode(9) = "                .PatternColorIndex = xlAutomatic"
    LeCode(10) = "            End With"
    LeCode(11) = "        End If"
    LeCode(12) = "    End If"
    LeCode(13) = "End Sub"
    Set Wb = ActiveWorkbook
    With Wb.VBProject.VBComponents("ThisWorkbook").CodeModule
        For i = .CountOfLines To 1 Step -1
            .DeleteLines i
            DoEvents
        Next
        For j = 1 To UBound(LeCode)
            .InsertLines j, LeCode(j)
        Next
    End With
End Sub

Le code supprime les lignes existantes avant d'écrire la macro.
Inconvénient de la méthode : Seule cette macro se trouvera dans ThisWorkbook, les éventuelles procédures existantes seront supprimées.
Pour placer plusieur macros, il faudrait identifier la macro EcrireDansThisWorkbook pour n'effacer qu'elle avant de l'écrire. C'est aussi possible...
Tu peux tester pour nous ?
A+

15/11/2007, 13h16
ouskel'n'or

Edit

Citation:

Envoyé par Pierre Fauconnier

Pour moi, devoir modifier le code par du code est toujours la plus mauvaise des solutions.
Si tu veux vraiment ajouter un module "en live", tu vas devoir passer par l'utilisation de vbcomponents, ... et, comme tu t'y es mis hier, je pense que ce n'est pas la meilleure approche pour démarrer avec VBA sur Excel, et cette solution, pour sympathique qu'elle puisse paraître, n'est à mon avis qu'un pis-aller...

Je suis d'accord avec toi là-dessus. Mais si l'on n'a pas d'autre solution...

Citation:

Envoyé par Pierre Fauconnier

Si tu souhaites utiliser la solution de Ouskel'n'or avec l'aide du tuto de Silkyroad, pense qu'il va falloir gérer le fait qu'il ne faut pas cliquer sur le bouton deux fois pour le même classeur...

Mon code règle le problème... si... et seulement si la macro est unique dans ThisWorkbook. Sinon, il faut le modifier.

Citation:

Envoyé par Pierre Fauconnier

[EDIT] Si, de plus, tu veux ajouter du code pour "jouer" avec des évènements sur tes feuilles, je te souhaite bon courage... N'oublie pas qu'en cas d'ajour par code, tu n'as pas droit à la moindre erreur dans ton code, car pas moyen de tester en debugage...[/EDIT]

+1 Encore d'accord :(
Avant d'insérer le code par macro, tu devras le tester dans un module placé dans l'un des classeurs que tu souhaites traiter par ce code.

Edit(re)
Le code proposé n'importe pas un module, comme indiqué dans la FAQ par SilkyRoad, mais écrit directement le code dans ThisWorkbook du classeur actif.
15/11/2007, 13h17
Pierre Fauconnier

Salut Ouskel'n'or... ( Je ne sais plus très bien moi-même, je navigue à vue, ce matin...)

Si il n'y a pas d'autre solution, alors, je pense que tant qu'à modifier le code de thisworkbook, autant le modifier pour qu'il ouvre un xla dans lequel on importe tout le code. Cela peut se faire par un classeur externe de mise à jour.

Avec cela, on n'interviendra avec l'utilisation de vbcomponents qu'une seule fois.

Après, si modification du code, il suffit de remplacer le viel XLA par le nouveau, et le tour est joué.

J'ai une solution en VBA qui tourne chez des clients. Dans cette solution, le xls ouvre le xla puis est piloté par le xla.

Si modifs, ajouts, ..., j'envoie un xla modifié, testé, vérifié, et le client remplace uniquement le xla.

Quel confort pour moi depuis que j'utilise cette solution...
15/11/2007, 13h26
ouskel'n'or

Hello Pierre,
Je suis assez d'accord pour passer par un xla mais comment vois-tu ça ? Un Addlins dans Perso.xls ? Un bouton qui active le xla ?
Je pense à jay-hut qui a débuté hier (!) mais je suis également intéressé. ;)
15/11/2007, 13h41
ouskel'n'or

Hello jay-hut,
Je reviens pour dire qu'il y a une erreur dans le code que tu nous as proposé. Tu as mis une parenthèse de trop sur cette ligne

Citation:

LeCode(3) = " If (Target.Value = ""GREY"") And Target.Column = 1) Then"

Je corrige "mon" code mais fais-en autant ;)

Voir 40 message(s) de cette discussion en une page

Fuseau horaire GMT +2. Il est actuellement 17h18.