Créer une feuille et la renommer

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

Worksheets.Add

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

ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")

il est possible que ce nom existe déjà et dans ce cas une erreur sera générée

Nous pourrions intercepter l’erreur en l’ignorant, comme l’illustre le code ci-dessous

On Error Resume Next ' Va a l'instruction suivante si erreur
ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")
On Error GoTo 0 ' Rend la main au code VBA

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.

If Err = 1004 Then ActiveSheet.Delete

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

Application.DisplayAlerts = False

Ce qui nous donne le code complet ci-dessous

Private Sub cmdRunAddSheet_Click()
 Worksheets.Add
 On Error Resume Next
 ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")
 If Err.Number = 1004 Then
  Application.DisplayAlerts = False
  ActiveSheet.Delete
  Application.DisplayAlerts = True
 End If
 On Error GoTo 0
End Sub

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

ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm") & "_" & Counter

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

On Error GoTo ErrHandler ' Renvoie à l'étiquette ErrHandler si Erreur

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.

ErrHandler:
 ' Traitement des erreurs
 With Err
  If .Number = 1004 Then
    Counter = Counter + 1 ' Incrémentation du compteur
    Resume
   Else
    MsgBox "Erreur " & .Number & " - " & .Description
  End If
 End With

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.

ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm") & IIf(Counter, "_" & Counter, "")

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

Private Sub cmdIncrementName_Click()
 ' Procédure d'insertion de feuille, nommage de celle-ci
 '     et incrémentation de son mom, s'il existe
 Dim Counter As Integer
 With Worksheets: .Add After:=Worksheets(.Count): End With
 On Error GoTo ErrHandler ' Renvoie à l'étiquette ErrHandler si Erreur
 ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm") & IIf(Counter, "_" & Counter, "")
 shtMenu.Activate ' Active la feuille Accueil (shtMenu est son codeName)
 On Error GoTo 0 ' Rend la main au VBA
Exit Sub ' Sortie de procédure
ErrHandler:
 ' Traitement des erreurs
 With Err
  If .Number = 1004 Then
    Counter = Counter + 1 ' Incrémentation du compteur
    Resume
   Else
    MsgBox "Erreur " & .Number & " - " & .Description
  End If
 End With
End Sub

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

[wpdm_package id=’865′]