Arrêt de VBA lors de la modification d'un CodeName par VBA

Version imprimable

10/01/2019, 23h30
PierreE_67207

Arrêt de VBA lors de la modification d'un CodeName par VBA

Bonjour à tous,

Je suis bloqué sur une ligne d'une procédure qui crée et manipule un Worksheet qui est la suivante :

Code:

ThisWorkbook.Worksheets(Nom_Onglet).Parent.VBProject.VBComponents(Worksheets(Nom_Onglet).CodeName).Properties("_CodeName") = Nouveau_Nom_Onglet

Ce qui se passe concrètement, c'est que les Userform qui étaient affichées disparaissent, exactement comme si je cliquais sur le bouton STOP de l'éditeur de Visual Basic (sauf que les lignes de codes qui suivent sont bien exécutées, donc ce n'est pas le bouton STOP).
Je suppose que ceci est dû à la manipulation du VBA par code...?

Auriez-vous déjà rencontré ce phénomène et existe-t-il une solution pour l'éviter, et ainsi conserver les Userform chargée ?

Je précise qu'à part ce phénomène, la procédure fonctionne sans planter, et que le résultat est bon. L'ennui c'est uniquement ce qui se passe avec cette fermeture de Userform...

Merci par avance,

PE
11/01/2019, 12h53
ARTURO83

Bonjour,

Si le formulaire se ferme après lecture de cette ligne, pourquoi ne pas le ré-ouvrir dans la foulée avec

Code:

Userform.show

Cdlt
11/01/2019, 14h09
PierreE_67207
Bonjour ARTURO83,

Merci pour ta réponse. Cela aurait effectivement pu être une manière de contourner le problème, mais ce n'est pas suffisant, car le problème est que l'userform est visiblement fermée ; conséquence :
- les informations saisies dans le formulaires sont perdues
- les valeurs assignées aux variables sont perdues
- l'userform ouvrant un autre fichier Excel (sans l'afficher), celui-ci n'est pas refermé, mais n'est plus accessible non plus
En résumé, le boulot de l'userform n'est pas terminé, et il faut recommencer.

Après faire des essais pour chercher à comprendre le comportement de VBA, je confirme qu'il ne s'agit pas d'une fermeture du formulaire (les événements QueryClose et Terminate de l'Userform ne sont pas détectés). En revanche c'est exactement le même comportement qui se produit si je clique sur le bouton STOP de Visual Basic ("Réinitialiser"). :aie::aie::aie:

Une autre idée peut-être ?

Merci par avance,

PE
11/01/2019, 14h47
Patrice740

Bonjour,

Essaies de créer un nouveau classeur dans lequel tu changes le CodeName de la feuille, puis déplaces la feuille dans Thisworkbook.
11/01/2019, 15h20
PierreE_67207

Bonjour Patrice740,

Merci de t'être intéressé à mon problème. Je vois ce que tu veux dire : créer la feuille dans un autre classeur et modifier son CodeName dans cet autre classeur, pour ensuite la ramener dans le bon classeur de travail, mais je crains que ça complique pas mal le code et alourdisse son exécution... :calim2: d'autant que je ne suis pas certain que le fait de faire cette création et modification de CodeName sur un autre classeur (mais toujours par l'exécution de VBA) résolve le problème :weird:

Merci quand même, mais je pense que je vais devoir trouver autre chose.

PE

Bonjour,

Citation:

Envoyé par PierreE_67207

... d'autant que je ne suis pas certain que le fait de faire cette création et modification de CodeName sur un autre classeur (mais toujours par l'exécution de VBA) résolve le problème

J'ai essayé ce code, qui ne ferme pas l'usf auquel il appartient :
Code:

1 2 3 4 5 6 7 Private Sub btnTest_Click() Dim W As Excel.Workbook Set W = Application.Workbooks.Add W.VBProject.VBComponents.Item(W.Worksheets(1).CodeName).Properties("_CodeName") = "shTest" W.Worksheets(1).Move after:=ThisWorkbook.Worksheets(1) W.Close False End Sub

12/01/2019, 12h06
Invité

Bonjour,
Modifier un code alors que la macro est en cours d'exécution ça s'appel un suicide!

Citation:

ThisWorkbook.Worksheets(Nom_Onglet).Parent.VBProject
12/01/2019, 13h39
Patrice740

Re,

Citation:

ThisWorkbook.Worksheets(Nom_Onglet).Parent....

Pourquoi demander à la fille de Pierre comment s'appelle son père ????
Le parent de la feuille d'un classeur, c'est le classeur !
13/01/2019, 23h29
PierreE_67207

Bonjour dysorthographie et Patrice740,

Je suis bien d'accord avec vous, ma ligne de code est maladroite ! quelque chose me faisait un peu mal aux yeux en la voyant mais ça n'avait pas fait tilte pour autant... :oops:

Cela dit, mon problème ne vient pas de là, les userform continuent de disparaître malgré la correction.

Patrice740, j'ai essayé ton bout de code, mais j'ai une erreur d'exécution '-2147221080 (800401a8)' erreur automation sur la ligne

Code:

W.Close False

J'ai donc essayé en la shuntant, et effectivement l'USF reste ouverte ! mais je ne comprends pas pourquoi dans ce cas oui, et pas dans mon cas ?

Je trouve la solution un peu tirée par les cheveux car mon but est de dupliquer une feuille "type" dans mon classeur, donc ici il faudrait que je crée une feuille et la renomme dans un autre classeur avant de la ramener dans Thisworkbook pour ensuite y copier le contenu de la feuille type ? sur le principe ça devrait fonctionner, quoique peut-être un chouia plus long à l'exécution. Dans cette solution il faudrait que je contourne l'erreur d'automation qui est générée par "W.Close False".

Dans l'attente de vous lire,

Bonne nuitée,

PE

Bonjour,

Citation:

Envoyé par PierreE_67207

Patrice740, j'ai essayé ton bout de code, mais j'ai une erreur d'exécution '-2147221080 (800401a8)' erreur automation sur la ligne W.Close False

L'erreur est due au fait que le classeur que tu crée ne contient qu'une seule feuille, une fois déplacée le classeur disparait (mais je ne sais pas ce qu'il devient). Pour corriger ce problème il est préférable de créer un classeur avec 2 feuilles:
Code:

1 2 3 4 5 6 7 8 9 10 11 Private Sub btnTest_Click() Dim W As Excel.Workbook Dim N As Long N = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 2 Set W = Application.Workbooks.Add Application.SheetsInNewWorkbook = N W.VBProject.VBComponents.Item(W.Worksheets(1).CodeName).Properties("_CodeName") = "shTest" W.Worksheets(1).Move after:=ThisWorkbook.Worksheets(1) W.Close False End Sub
Il est préférable de modifier le code d'un autre classeur que celui en cours d'exécution, comme te l'a dit Dysorthographie

Citation:

Modifier un code alors que la macro est en cours d'exécution ça s'appelle un suicide!

Par contre tu dis :

Citation:

... car mon but est de dupliquer une feuille "type" dans mon classeur

Dans ce cas pourquoi modifier son CodeName ??? Il y a très certainement une solution mieux adaptée ...

14/01/2019, 05h44
Philippe Tulliez
Bonjour,

Citation:

Pour corriger ce problème il est préférable de créer un classeur avec 2 feuilles:

Ou tout simplement remplacer la méthode Move par Copy dans la ligne ci-dessous puisque la ligne qui suit ferme le classeur sans le sauver
Code:

1 2 W.Worksheets(1).Move after:=ThisWorkbook.Worksheets(1) W.Close False
Citation:

Dans ce cas pourquoi modifier son CodeName ??? Il y a très certainement une solution mieux adaptée ...

Je me pose la même question
14/01/2019, 08h24
Theze

Bonjour,

Citation:

Dans ce cas pourquoi modifier son CodeName ??? Il y a très certainement une solution mieux adaptée ...

C'est exactement la question que je lui ai posé sur l'autre forum !!!
14/01/2019, 10h52
PierreE_67207

Bonjour à tous, et merci de vos réponses très constructives ! :)

Merci Patrice pour le bout de code modifié, je pense que je vais passer par cette solution, si j'arrive à récupérer facilement les plages nommées de la feuille type... à voir donc.

Citation:

Dans ce cas pourquoi modifier son CodeName ??? Il y a très certainement une solution mieux adaptée ...

Je pensais justement que c'était une solution bien adaptée : dans mon code initiale je n'agissais que sur les Name, mais je me suis dit qu'il était préférable de prendre en compte les risques de modification de nom des onglets par les utilisateurs, c'est pour ça que j'ai décidé d'appeler mes feuilles par leur CodeName. Par ailleurs ça me permet aussi d'utiliser le CodeName comme "mémoire" du Name : si le Name est modifié, je peux faire un contrôle avec la valeur du CodeName pour décider de la suite à donner.
Je trouvais aussi plus "propre" de travailler avec les CodeName, mais c'est peut-être une idée reçue....
Toutefois si ça complique la chose, je vais peut-être retourner en arrière et ne travailler qu'avec les Name des feuilles manipulées et créées, ça ne me semble pas irréversible.

Citation:

C'est exactement la question que je lui ai posé sur l'autre forum !!!

Désolé si mon poste fait doublon avec un autre forum, j'avais trouvé utile de demander ailleurs aussi pour avoir plus de chance de régler mon problème, mais je vois que ce n'est pas nécessaire puisque les différents membres sont sur les deux forums ;)

PE
14/01/2019, 12h23
Patrice740

Bonjour,

Citation:

Envoyé par PierreE_67207

... dans mon code initiale je n'agissais que sur les Name, mais je me suis dit qu'il était préférable de prendre en compte les risques de modification de nom des onglets par les utilisateurs, ...

Je n'ai jamais eu besoin de changer le CodeName d'une feuille. Sans rentrer dans le détail de ton code, lorsque tu copies une feuille, les macro qu'elle contient sont aussi dupliquées, la racine du CodeName reste la même, seul le numéro change, il est donc simple d'identifier une feuille dont le Name a changé.
14/01/2019, 13h45
Philippe Tulliez

Bonjour,

Citation:

Je trouvais aussi plus "propre" de travailler avec les CodeName, mais c'est peut-être une idée reçue....

Travailler avec le CodeName est certainement une bonne pratique et même conseillée mais une fois le CodeName renommé, je ne vois pas l'intérêt de le renommer à nouveau.

Citation:

si le Name est modifié, je peux faire un contrôle avec la valeur du CodeName pour décider de la suite à donner.

Personnellement, je ne vérifie jamais si le nom à été modifié puisque l'utilisation du CodeName permet justement de s'affranchir du risque que l'utilisateur modifie le nom de la feuille.
14/01/2019, 14h08
Patrice740

Re,

Citation:

Envoyé par PierreE_67207

... Je suis bloqué sur une ligne d'une procédure qui crée et manipule un Worksheet

Citation:

Envoyé par Philippe Tulliez

Personnellement, je ne vérifie jamais si le nom à été modifié puisque l'utilisation du CodeName permet justement de s'affranchir du risque que l'utilisateur modifie le nom de la feuille.

Le problème est qu'on ne peut pas utiliser directement le CodeName de la nouvelle feuille sans modifier dynamiquement le code.
14/01/2019, 14h26
Philippe Tulliez

Bonjour Patrice,

Citation:

Le problème est qu'on ne peut pas utiliser directement le CodeName de la nouvelle feuille sans modifier dynamiquement le code.

Je faisais allusion à mon cas personnel.
Lorsque j'écris une application, je renomme manuellement le CodeName des feuilles du classeur que j'utilise dans mon code VBA et je n'ai donc pas besoin de les renommer ultérieurement.

Je n'utilise d'ailleurs jamais d'instruction utilisant la propriété VBProject car le risque est trop grand que l'option Accès approuvé au modèle d'objet du projet VBA soit décochée
14/01/2019, 21h23
PierreE_67207

Bonjour à tous, Philippe et Patrice,

Les derniers échanges me poussent donc à ne pas renommer mes feuilles créées (par copie d'une feuille type), qui pourront être identifiées dans le CodeName, d'une part par la racine (CodeName de la feuille type) et d'autre part par l'indice générée lors de la copie. C'est une autre manière d'aborder le problème, mais tout aussi efficace finalement.

Donc merci encore pour vos réponses, finalement la réponse à mon problème de modification de CodeName était tout simplement de ne pas y modifier le CodeName (par code) !

Je considère le post résolu, et retiens qu'il ne faut pas modifier les propriétés de projet VBA par code ;)

PE