[XL-2007] Boutons placés par macro sur feuille ne fonctionnent pas [Résolu]

[Danad38]

Bonjour à tous,

Je travaille avec Excel 2007. Je veux créer une macro qui puisse :
1 - Ouvrir un fichier déjà enregistré sur le disque.
2 - Placer 2 CommandButton sur celle-ci. L'un servira pour le réenregistrement (après modification de la feuille) du fichier avec le même nom. Le second lancera une impression.

Mon problème.
Après des recherches j'ai trouvé des "bouts de code". Ceux-ci me permettent actuellement d'ouvrir un fichier et de placer sur la feuille les deux boutons.
Je ne parviens cependant pas à "commander ces boutons" ... (Je connais les procédures d'enregistrement et d'impression qui fonctionnent parfaitement avec des boutons placés préalablement sur une feuille ouverte).
Dans ce cas, tous ce que je peux écrire ne fonctionne pas.

Je m'interroge aussi sur le fait, qu'une fois ouvert, la procédure doit, après avoir placé les boutons, me rendre la main pour les modifications.
Les boutons sur la feuille me permettant de terminer "le travail".

J'espère avoir été assez clair sur ma demande d'aide.

Voici le début du code utilisé ... Pour la suite, je suis dans le vide ...
(Les MsgBox ne sont là que pour me permettre de suivre la procédure, elles disparaitront à la fin).

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 Réouverture_Fichier()
 
Dim NomClasseur As Workbook
Dim Bouton_Enr As OLEObject
Dim Bouton_lmp As OLEObject
 
MsgBox "Vous allez ouvrir l'arborescence des fichiers. Choisissez votre répertoire et le fichier à ouvrir."
 
Application.Dialogs(xlDialogOpen).Show
 
MsgBox "Le nom du classeur actif est : " & ActiveWorkbook.Name
 
Set NomClasseur = ActiveWorkbook
 
Set Bouton_Enr = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=100, Top:=40, Width:=100, Height:=20)
Set Bouton_lmp = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=100, Top:=75, Width:=100, Height:=20)
 
Bouton_Enr.Name = "Bouton_Enr"
Bouton_lmp.Name = "Bouton_lmp"
 
Bouton_Enr.Object.Caption = "Enregistrement"
Bouton_lmp.Object.Caption = "Impression"
 
 
End Sub

Je vous remercie par avance pour votre aide.

Danad38

[jfontaine]

Bonjour,

Si je te suis, il te manque le code associé aux boutons.

Le code ci dessous ajoute une procédure Click au bouton "Bouton_Enr"

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub AjoutCode()
'Référence à ajouter Microsoft Visual Basic for Application Extsensibility 5.3
Dim Usf As VBComponent
Dim i As Integer
 
Set Usf = ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName)
 
With Usf.CodeModule
    i = .CountOfLines
    .InsertLines i + 1, "Private Sub Bouton_Enr_Click()"
    .InsertLines i + 2, " 'Code"
    .InsertLines i + 3, "End Sub"
End With
 
End Sub

Ici de la documentation

[Danad38]

Bonjour Jérome et merci pour ta réponse,

J'ai commencé par lire et essayé de comprendre la doc vers laquelle tu me dirigeais. J'ai trouver "ma question" mais après quelques heures d'essais, ce n'est pas concluant.
Novice en VBA, voici donc mes questions (certainement naïves, mais ...).
1 - Pourquoi parles-tu d'"Usf" (UserForm ?). Quel est son rôle ?
2 - Où placer ce code par rapport au mien ? Comment relier ce que j'ai écrit avec ce que tu me fournis ?
3 - Je suppose qu'entre With et End With, les lignes de code écrites en rouge doivent correspondre à mon code d'enregistrement ? C'est à dire que si ce code fait 10 ligne je devrais ajouter les lignes supplémentaires du style : .InsertLines i + 4, "...ligne de code", puis .InsertLines i + 5, "... ligne de code" ... ?

Merci encore, je vais me replonger dans la doc. Il va bien falloir que je comprenne !

Bonne soirée.

Danad38

[jfontaine]

1 - Pourquoi parles-tu d'"Usf" (UserForm ?). Quel est son rôle ?

Usf est une variable, on peut lui donner le nom que l'on souhaite.

2 - Où placer ce code par rapport au mien ? Comment relier ce que j'ai écrit avec ce que tu me fournis ?

Ce code est a placer après la création des Boutons

3 - Je suppose qu'entre With et End With, les lignes de code écrites en rouge doivent correspondre à mon code d'enregistrement ? C'est à dire que si ce code fait 10 ligne je devrais ajouter les lignes supplémentaires du style : .InsertLines i + 4, "...ligne de code", puis .InsertLines i + 5, "... ligne de code" ... ?

oui, c'est bien ça

[Danad38]

Jérome,

Décidément, ça coince encore. Il se passe "des choses" que je ne comprends pas. Le mieux est que je reprenne depuis le début ce que j'ai fait.
1 - Je crée des Factures/Devis à l'aide d'un UserForm. Les données sont ensuite placées dans une feuille du classeur nommée : Feuil22(Facture - Devis en cours). Sur cette feuille j'ai placé 3 boutons (Enregistrer, Imprimer et Effacer). Prenons le premier bouton : Enregistrer. Relié à celui-ci j'ai placé le code suivant :

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
26
27
28
29
30
31
32
33
34
35
Sub Enregistrement_Factures_Devis()
 
Dim NumCom As Integer
Dim Nom As String
Dim FacDev As String
 
Range("C5").Select
NumCom = ActiveCell.Value
ActiveCell.Offset(2, 0).Select
Nom = ActiveCell.Value
ActiveCell.Offset(-4, 2).Select
FacDev = ActiveCell.Value
 
ActiveSheet.Copy
 
If FacDev = "Facture" Then
        CheminFichier = ThisWorkbook.Path & "\" & "Factures" & "\"
        NomFichier = Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "--" & NumCom & "-" & Nom & ".xls"
 
        With ActiveWorkbook
        MsgBox "La facture sauvegardée porte la référence : " & NomFichier
            .SaveAs Filename:=CheminFichier & NomFichier
            .Close
        End With
    Else: CheminFichier = ThisWorkbook.Path & "\" & "Devis" & "\"
        NomFichier = Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "--" & NumCom & "-" & Nom & ".xls"
 
        With ActiveWorkbook
        MsgBox "Le devis sauvegardé porte la référence : " & NomFichier
            .SaveAs Filename:=CheminFichier & NomFichier
            .Close
        End With
End If
 
End Sub

Ces trois boutons fonctionnent correctement.

2 - Maintenant je souhaite pouvoir rappeler une Facture/Devis pour modification, et je souhaite qu'après l'ouverture il se crée 3 boutons : Enregistrer, Imprimer et Effacer.

Voici mon code pour l'ouverture et la création du premier 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
26
27
28
29
30
Sub Réouverture_Facture_ou_Devis()
 
Dim NomClasseur As Workbook
Dim Bouton_Enr As OLEObject
Dim Bouton_lmp As OLEObject
Dim Ws As Worksheet
Dim Enregistrer As String
Dim Imprimer As String
 
MsgBox "Vous allez ouvrir l'arborescence des fichiers. Choisissez votre répertoire et le fichier à ouvrir."
 
Application.Dialogs(xlDialogOpen).Show
 
MsgBox "Le nom du classeur actif est : " & ActiveWorkbook.Name
 
Set NomClasseur = ActiveWorkbook
 
Set Bouton_Enr = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1")
With Bouton_Enr
    .Name = "Enregistrer"
    .Left = 10
    .Top = 30
    .Width = 60
    .Height = 20
    .Object.Caption = "Enregistrer"
End With
 
AjoutCode
 
End Sub

La ligne "AjoutCode" renvoie au programme que tu m'as fourni et qui est sensé ouvrir une MsgBox (C'est simplement pour savoir si le click sur mon bouton "Enregistrer" fonctionne). J'ai remplacé Usf par CeClasseur.
Voici ce programme :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub AjoutCode()
 
Dim CeClasseur As VBComponent
Dim i As Integer
Dim Message As String
 
Message = "On enregistre !"
 
Set CeClasseur = ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName)
 
With CeClasseur.CodeModule
    i = .CountOfLines
    .InsertLines i + 1, "Private Sub Enregistrer_Click()"
    .InsertLines i + 2, "msgBox Message"
    .InsertLines i + 3, "End Sub"
End With
 
End Sub

3 - Voici maintenant ce qui se passe lorsque je lance ma procédure avec F8.

a - La feuille choisie s'ouvre.
b - Le bouton se crée. (Même si la taille de la police laisse à désirer ... mais ce n'est pas le problème essentiel).
c - Lorsque le sous-programme AjoutCode se lance, il se crée, lié à la feuil22(Facture - Devis en cours), un module appelé : Truc.xlsm - Feuil22(Code) avec un programme nommé : Enregistrement_Factures_Devis (Comme celui relié au bouton enregistrer de ma feuille initiale) et dont le code est :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Enregistrement_Factures_Devis()
 
ActiveSheet.Copy
 
CheminFichier = ThisWorkbook.Path & "\" & "Factures" & "\"
NomFichier = Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "--" & NumCom & "-" & Nom & ".xls"
 
With ActiveWorkbook
MsgBox "La sauvegarde porte la référence : " & NomFichier
    .SaveAs Filename:=CheminFichier & NomFichier
    .Close
End With
 
End Sub

d - A la suite de ce programme, il se crée aussi un nouveau sous programme de la manière suivante :

Code :

Private Sub Enregistrer_Click()

Puis un message d'erreur s'affiche me disant :
"Impossible d'entrer en mode Arrêt maintenant". Les choix sont : Continuer - Fin - Aide. Si je clique sur Continuer, alors deux lignes supplémentaires s'inscrivent :

Code :

1
2
MsgBox Message
End Sub

Bien sûr, lorsque je retourne sur ma feuille ouverte, le click sur le bouton ne provoque rien ...

Où est l'erreur ?

Encore merci pour le temps passé à me répondre.

Danad38

[jfontaine]

3 - Voici maintenant ce qui se passe lorsque je lance ma procédure avec F8.

Cette erreur survient lorsque l'on utilise le mode pas a pas sur cette ligne

Code :

    .InsertLines i + 1, "Private Sub Enregistrer_Click()"

Il faut exécuter normalement et il n'y aura pas d'erreur

A noter qu'il faut écrire comme cela pour prendre en compte la valeur de l avariable et non la variable. Si tu souhaites avoir la variable dans le code, il faut modifier sa portabilité (déclaration en public dans un module)

Code :

1
2
3
4
5
6
With CeClasseur.CodeModule
    i = .CountOfLines
    .InsertLines i + 1, "Private Sub Enregistrer_Click()"
    .InsertLines i + 2, "msgBox """ & Message & """"
    .InsertLines i + 3, "End Sub"
End With

[Danad38]

Boujour Jérome,

Quand ça ne veux pas ! ...
Merci pour ton explication sur mon erreur, en effet si je lance ma procédure directement, tout se passe normalement.

Voici l'état de mon code actuellement :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Public Message As String
Sub Réouverture_Facture_ou_Devis()
 
Dim NomClasseur As Workbook
Dim Bouton_Enr As OLEObject
Dim Bouton_lmp As OLEObject
Dim Ws As Worksheet
Dim Enregistrer As String
Dim Imprimer As String
 
MsgBox "Vous allez ouvrir l'arborescence des fichiers. Choisissez votre répertoire et le fichier à ouvrir."
 
Application.Dialogs(xlDialogOpen).Show
 
MsgBox "Le nom du classeur actif est : " & ActiveWorkbook.Name
 
Set NomClasseur = ActiveWorkbook
 
Set Bouton_Enr = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1")
With Bouton_Enr
    .Name = "Enregistrer"
    .Left = 10
    .Top = 30
    .Width = 60
    .Height = 20
    .Object.Caption = "Enregistrer"
End With
 
AjoutCode
 
End Sub
 
 
Sub AjoutCode()
'Référence à ajouter Microsoft Visual Basic for Application Extsensibility 5.3
Dim CeClasseur As VBComponent
Dim i As Integer
'Dim Message As String
 
Message = "On enregistre !"
 
Set CeClasseur = ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName)
 
With CeClasseur.CodeModule
    i = .CountOfLines
    .InsertLines i + 1, "Private Sub Enregistrer_Click()"
    .InsertLines i + 2, "msgBox """ & Message & """"
    .InsertLines i + 3, "End Sub"
End With
 
End Sub

Lorsque je lance ce programme :
1 - La feuille choisie s'ouvre.
2 - Le bouton "Enregistrer" se positionne.

Quelques précisions qui pourront peut-être te sembler utiles.

J'ai donc un classeur "Principal" contenant toutes mes feuilles et macros.
Dans ce classeur la feuille nommée : Feuil22(Facture - Devis en cours) ne possède pas de module lié.
Une fois le fichier ouvert j'ai donc un second classeur ouvert qui ne comporte qu'une feuille "utile" nommée elle aussi : Feuil22(Facture - Devis en cours).

Je constate qu'une fois mon programme d'ouverture de fichier effectué, j'ai un module qui se crée avec le code suivant :

Code :

1
2
3
Private Sub Enregistrer_Click()
MsgBox "On enregistre !"
End Sub

Or ce module est lié à la feuille : Feuil22(Facture - Devis) de mon classeur principal ... Cette même feuille du classeur ouvert par la procédure (celle contenant le bouton) de dispose d'aucun module ...

Est-ce la raison qui fait que, dans ma feuille : Feuil22(Facture - Devis en cours) du classeur ouvert, le bouton n'amène aucune action ?

Si oui, comment remédier à ce problème ?

Il est difficile, à distance, de bien appréhender la nature du problème quelquefois. Ce qui me parrait "clair" ne l'est pas forcément pour toi.

Merci encore et bonne journée.

Danand38

[jfontaine]

Essai en remplacant Thisworkbook par Activeworkbook

Code :

Set CeClasseur = Activeworkbook.VBProject.VBComponents(ActiveSheet.CodeName)

[ZebreLoup]

Pour éviter ce problème de code créé dynamiquement (pas simple à gérer, problèmes de sécurité, ...), ce ne serait pas plus facile d'avoir un classeur vierge qui contient déjà les boutons et les macros que tu veux, de copier le contenu du classeur que tu veux ouvrir dans ce classeur temporaire et qu'au moment de la sauvegarde, tu l'enregistres sous le nom du classeur original ?

C'est juste une idée comme ça. J'avoue que je trouve ça souvent un peu dangereux de travailler sur l'objet VBProject, surtout si on n'est pas expert.

[Danad38]

Jérôme, ZebreLoup,

Merci à vous deux.
Cette fois ça marche !

En effet, Jérôme, le fait de remplacer ThisWorkbook par ActiveWorkbook a solutionné le problème.

Quant à ta solution, ZebreLoup, je pensais m'y acheminer si je n'avais pas pu résoudre cette difficulté.

En conclusion, quelle que soit la solution finale que je retiendrai, j'ai appris beaucoup sur ce genre de manipulation que je ne connaissais pas.

Merci encore et bon courage pour votre engagement.

Danad38