Comment incrémenter le nom d’une feuille Excel, lorsqu’il existe déjà ?

Créer une feuille et la renommer

Lorsque l’on crée dynamiquement une feuille dans un classeur Excel à l’aide de l’instruction

et que l’on souhaite lui attribuer un nom particulier, par exemple le texte CA suivi de l’année et du numéro du mois en cours

il est possible que ce nom existe déjà et dans ce cas une erreur sera générée
Erreur 1004 - Renomer une feuille
Nous pourrions intercepter l’erreur en l’ignorant, comme l’illustre le code ci-dessous

Ce qui aura pour effet d’éviter l’affichage du message d’erreur mais qui nous laissera la feuille récemment créée avec son nom incrémenté comme par exemple Feuil7 ou Sheet7 suivant la langue de l’interface.
Il nous reste à faire en sorte de supprimer cette feuille dans le cas où le nom existe déjà. A l’aide de l’instruction qui suit.

La suppression d’une feuille engendre l’affichage d’un message
« Les feuilles sélectionnées peuvent contenir des données. Pour supprimer définitivement les données ,cliquer sur SUPPRIMER »
Pour éviter l’affichage de ce message, nous ajouterons l’instruction qui suit, juste avant l’instruction de suppression

Ce qui nous donne le code complet ci-dessous

Ce code est une procédure événementielle associée à un contrôle CommandButton de type ActiveX nommé  cmdRunAddSheet.

Lors d’une formation  « VBA pour excel » que je dispensais la semaine dernière, une participante m’a posé une question intéressante qui est le sujet finalement de ce billet.

Comment incrémenter le nom, s’il existe déjà ?

Dans notre exemple, nous avons choisi comme nom le mot CA suivi de l’année et du numéro du mois soit pour ce mois ci CA 2016-03.

L’idée est donc d’avoir une incrémentation de ce nom et donc d’avoir CA 2016-03_0, CA 2016-03_1, CA 2016-03_2, etc.

Soit pour la création du nom, l’instruction

Counter étant la variable contenant le n° incrémenté.

Pour réaliser cela, nous allons gérer l’erreur en utilisant l’instruction GoTo qui renverra à une étiquette nommée ErrHandler à la place de l’instruction Resume Next

Traitement de l’erreur

Dans cette procédure, nous vérifions si le numéro de l’erreur est bien 1004 et dans l’affirmative nous allons incrémenter la variable Counter. Dans la négative, un message doit s’afficher pour indiquer le numéro et la description de cette erreur. C’est une précaution qui permet d’avoir une indication d’interception d’erreur non prévue.

Cette question résolue en entraîne une autre, « Comment faire en sorte de n’avoir l’affichage du numéro qu’à partir du numéro 1 ? »

Soit CA 2016-03, CA 2016-03_1, CA 2016-03_2, etc.

Nous utiliserons pour créer le nom, la concaténation du mot CA suivi de la date formatée et la fonction IIF qui renverra soit le numéro incrémenté soit une chaîne vide.

Voici donc le code complet de la procédure événementielle nommée cmdIncrementName_Click

La méthode Add

Pour insérer une feuille de calcul dans un classeur, on utilise la méthode Add de la collection WorkSheets.

Cette méthode a plusieurs arguments facultatifs (Before, After) dont la valeur indique la position avant ou après où aura lieu l’insertion

Exemples

Worksheets.Add ‘ Insère une feuille avant la feuille active

Worksheets.Add Before:=Worksheets(1) ‘ Insère une feuille avant la première

Worksheets.Add After:=Worksheets(1)     ‘ Insère une feuille après la première

Worksheets.Add After:=Worksheets(Worksheets.Count) ‘ Insère une feuille après la dernière

Liens

Pour en savoir plus sur la gestion des erreurs, je vous conseille la lecture de ce tutoriel de Michel Tanguy (SilkyRoad) La gestion des erreurs dans Excel

Classeur exemple à télécharger

Deux procédures en exemple dans ce classeur

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. Apprenez comment les données de vos commentaires sont utilisées.