Renier a un classeur qui contient dix feuilles de calcul, nommées Ressources 1 à Ressources 10. S’il supprime l’une de ces feuilles de calcul (par exemple, Ressources 3), Renier aimerait que les feuilles de calcul restantes soient renommées, automatiquement, pour « combler l’écart , » pour ainsi dire. Ainsi, les ressources 4 à 10 seraient automatiquement renommées pour devenir Ressources 3 à travers les ressources 9.

Il existe plusieurs façons de réaliser cette tâche, tout en utilisant des macros. Une approche est probablement moins automatique que ce que Renier préférerait, mais elle fonctionne très bien:

Sub ReNameSheets()

Dim J As Integer     Dim wks As Worksheet

J = 0     For Each wks In ActiveWorkbook.Worksheets         J = J + 1         wks.Name = "TempSheet " & J     Next wks

J = 0     For Each wks In ActiveWorkbook.Worksheets         J = J + 1         wks.Name = "Resources " & J     Next wks End Sub

Cette macro parcourt simplement toutes les feuilles de calcul, en les renommant en utilisant la convention préférée de Renier. La macro peut être exécutée à la demande, chaque fois qu’un changement de nom des feuilles de calcul est souhaité.

Notez que le code effectue en fait deux passes de dénomination dans les feuilles de calcul. La première consiste à définir les noms des feuilles de calcul sur des noms temporaires et la seconde à les définir sur des noms définitifs. Les deux passes permettent d’éviter un problème potentiel lorsque vous ajoutez des feuilles de calcul au classeur – par exemple, si vous ajoutez une nouvelle feuille de calcul juste avant Resources 4, un changement de nom en une seule passe essaierait de renommer la nouvelle feuille de calcul en Resources 4, ce qui générerait une erreur parce que la feuille de calcul 5 serait, à ce stade, également nommée Resources 4. En effectuant deux passes, vous renommez tout en quelque chose d’entièrement nouveau, puis définissez le nom final.

Pour une approche plus automatique, vous pouvez envisager d’utiliser l’événement SheetBeforeDelete pour l’objet Workbook. Cet événement est déclenché (comme son nom l’indique) immédiatement avant la suppression d’une feuille de calcul. Voici un exemple d’approche de gestionnaire d’événements:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

Dim sPrefix As String     Dim iNum1 As Integer     Dim iNum2 As Integer     Dim wks As Worksheet

sPrefix = "Resources "

If Left(Sh.Name, Len(sPrefix)) = sPrefix Then         iNum1 = CInt(Right(Sh.Name, Len(Sh.Name) - Len(sPrefix)))

Sh.Name = "TempSheet 9999"

For Each wks In Sheets             If Left(wks.Name, Len(sPrefix)) = sPrefix Then                 iNum2 = CInt(Right(wks.Name, Len(wks.Name) - Len(Prefix)))

If iNum2 > iNum1 Then                     wks.Name = sPrefix & (iNum2 - 1)

End If             End If         Next     End If End Sub

Le gestionnaire d’événements vérifie si la feuille de calcul en cours de suppression commence par le préfixe désigné («Resources»). Si tel est le cas, il renomme la feuille de calcul supprimée en un nom temporaire (« TempSheet 9999 »).

Ensuite, il parcourt chacune des feuilles de calcul dans le classeur et renomme toute feuille de calcul qui a le préfixe approprié et un numéro de suffixe plus élevé que la feuille de calcul qui est supprimée.

Il y a deux grandes mises en garde avec cette approche. Tout d’abord, cela ne gérera pas si vous ajoutez des feuilles de calcul. Deuxièmement, il ne gérera de manière fiable que la suppression de feuilles de calcul uniques. Si vous supprimez plusieurs feuilles de calcul à la fois, la numérotation sera incorrecte et, en fait, la macro peut se bloquer en raison de la manière dont Excel gère les suppressions.

Si vous prévoyez de supprimer plusieurs feuilles de calcul assez souvent, vous pouvez contourner le problème en utilisant une approche différente. Celui-ci repose sur l’utilisation des événements SheetActivate et SheetDeactivate de l’objet Workbook.

' These variables are declared OUTSIDE of the events, ' so they are available globally.



Dim shName As String     Dim Avail As Variant

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

' When the worksheet is deactivated, the name of     ' that worksheet is stored in the shName variable.



shName = Sh.Name End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim wks As Worksheet     Dim J As Integer

On Error Resume Next     ' The following line will generate an error if the     ' the worksheet shName was deleted. If the error     ' occurrs, then we can trigger the renaming.

Avail = Sheets(shName).Range("A1")

If Err Then         J = 0         For Each wks In ActiveWorkbook.Worksheets             J = J + 1             wks.Name = "Resources " & J         Next wks     End If     On Error GoTo 0 End Sub

Ces gestionnaires d’événements fonctionnent car l’événement SheetDeactivate est déclenché automatiquement chaque fois qu’une feuille de calcul est laissée (par exemple, lorsque vous activez une feuille de calcul différente) ou chaque fois qu’une feuille de calcul est supprimée. L’événement SheetActivate est alors déclenché et il vérifie, essentiellement, si la feuille précédemment désactivée peut être atteinte ou non. S’il ne peut pas être atteint, nous savons qu’il a été supprimé et nous pouvons déclencher un changement de nom de toutes les feuilles de calcul.

L’approche que vous utilisez pour renommer dépend entièrement de vous en fonction de la façon dont vous souhaitez travailler avec vos feuilles de calcul.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13572) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.