renommer onglet par macro, appel impossible

[hellle]

Bonjour,

après deux ans d'interruption excellistiques, et pas de progrès entre temps, hélas, me rewalou avec un problème de débutante (toujours...) !

Dans le fichier de planning ci joint, (allégé de 9 onglets-mois), j'ai deux bouts de code accolés pour le rafraichissement annuel des noms d'onglets. Que voici :

Private Sub Workbook_Open()

'Sub RenommeOnglets/Y3

Dim Feuille As Worksheet, Boucle As Long

Boucle = 1
For Each Feuille In Worksheets
Feuille.Name = Sheets(1).Range("Y3").Value & Boucle
Boucle = (Boucle + 1)
Next Feuille

For Each Feuille In Worksheets
Feuille.Name = Feuille.Range("Y3").Value
Next Feuille


End Sub


Problème numéro 1 : cette macro se déclenche à l'événement "open", ou à l'execution directe, mais impossible de l'appeler, que ce soit par le biais d'une autre macro, ou, ce que je voudrais, par le biais de la fonction feuille 1, en f3 (j'avais bien sur inscrit la macro dans un module pour cette action, mais aucun effet).
Donc, la question est peut-être : que ne comprends-je pas dans l'appel d'une macro ?

Problème number 2 : l'adjonction de :

Dim Feuille As Worksheet, Boucle As Long

Boucle = 1
For Each Feuille In Worksheets
Feuille.Name = Sheets(1).Range("Y3").Value & Boucle
Boucle = (Boucle + 1)
Next Feuille

n'est là que pour permettre l'exécution complète de :


"For Each Feuille In Worksheets
Feuille.Name = Feuille.Range("Y3").Value
Next Feuille"

qui, employé seul, s'arréte au premier onglet-doublon rencontré (par exemple, l'onglet fraichement renommé est identique au suivant, non encore renommé)

Y aurait-il un moyen plus simple (et rapide car le double renommage prend double de temps, du coup) de contourner ce problème ?

Merci à ce forum d'exister, même quand je ne fais pas de demande directe, je solutionne la plupart de mes problèmes grace à vous !

 

[Hippolite]

Re : renommer onglet par macro, appel impossible

Bonsoir,
Ton code toileté :

Private Sub Workbook_Open() 'à placer dans ThisWorkbook
	RenommeOnglets
End Sub


Public Sub RenommeOnglets() 'à placer dans un module
    Dim i As Long, nS As Long, NomTemp As String
    'gel de l'écran
    Application.ScreenUpdating = False
    'gel du calcul
    Application.Calculation = xlCalculationManual
    NomTemp = Sheets(1).Range("Y3") 'mis en paramètre pour ne pas le recalculer à chaque boucle
    nS = Sheets.Count 'nombre de feuilles, mis en paramètre pour le calculer une seule fois
    For i = 1 To nS 'ce type de boucle est plus rapide que For Each...In...
        Sheets(i).Name = NomTemp & i
    Next i
    For i = 1 To nS
        Sheets(i).Name = Sheets(i).Range("Y3").Value
    Next i
    'rétablissement du calcul automatique
    Application.Calculation = xlCalculationAutomatic
    'rétablissement du rafraichissement de l'écran
    Application.ScreenUpdating = True
End Sub

J'ai gelé l'affichage et les calculs pendant l'exécution du code pour réduire le temps d'exécution.
La macro, placée dans un module, peut être appelée partout.
A+

 

[hellle]

Re : renommer onglet par macro, appel impossible

Waooooo !

Ce n'est plus du toilettage à ce niveau, mais un relooking complet !
Et c'est devenu un vrai petit bolide !
Merci à toi, vraiment.
Si tu peux me décrypter ton code, ça m'aide à avancer quand j'ai le décodeur -

En revanche, toujours aucune réaction à l'appel depuis la formule : =SI(F2<>Y3;RenommeOnglets;"").
Je soupçonne que la panne est entre le fauteuil et le clavier, ça fait des jours que je suis à la recherche de mon neurone manquant,
si quelqu'un sait où il peut être.... -)

 

[Hippolite]

Re : renommer onglet par macro, appel impossible

Bonjour,

J'ai ajouté des commentaires dans le code.

On ne peux pas appeler une macro par une formule dans une feuille, mais tu peux utiliser une macro événementielle pour tester si F2 ou Y3 sont modiifiés et lancer une action :
à placer dans Feuil1

Private Sub Worksheet_Change(ByVal Target As Range)
    'si F2 ou Y3 sont modifiés, alors...
    If Not Intersect(Target, Union(Range("F2"), Range("Y3"))) Is Nothing Then
        If Range("F2") <> Range("Y3") Then RenommeOnglets
    End If
End Sub

A+

 

[hellle]

Re : renommer onglet par macro, appel impossible

Bonjour,


Merci pour les explications.
A force de tournicoter, j'avais fini par comprendre qu'on ne pouvait appeler une macro dans une formule, à moins de la mettre en "Function".

Cependant, la macro d'appel ne fonctionne pas sur F2 et Y3, qui contiennent des résultats de formule.
Elle fonctionne sur des résultats entrés "en dur".
Si j'ai bien tout compris, pour prendre en compte des résultats de formule, il faudrait passer par l'événement Calculate, plutot que Change.
Mais y a-t-il un moyen pour que Calculate ne prenne en compte que le recalcul que ces deux cellules, et non pas de la feuille entière ?

Ou sinon, est-il possible de transformer la première macro en Function ?

Merci de ta patience...

 

[Hippolite]

Re : renommer onglet par macro, appel impossible

Re,

est-il possible de transformer la première macro en Function ?

Remplace Sub par Function et ajoute une ligne pour ce que la fonction renvoie par exemple

RenommeOnglets = True

A+

 

[Hippolite]

Re : renommer onglet par macro, appel impossible

Re,
Il faudrait que tu mettes dans la feuille

Private Sub Worksheet_Calculate()
If (Range("F2") <> Range("Y3")) Then RenommeOnglets
End Sub

A+

 

[hellle]

Re : renommer onglet par macro, appel impossible

Bonsoir,

désolée de faire le boulet...

Mais après avoir entré tes deux nouveaux bouts de code, j'ai une erreur 1004 et ceci en débogage :


Sheets(i).Name = Sheets(i).Range("Y3").Value

 

[Hippolite]

Re : renommer onglet par macro, appel impossible

Re,
Envoie la dernière version de ton fichier, un bout de code isolé ne permet pas d'analyse.
A+

 

[hellle]

Re : renommer onglet par macro, appel impossible

Re :

Wala la bestiole

 

[Hippolite]

Re : renommer onglet par macro, appel impossible

Bonjour,
Pour que ça se mette à jour automatiquement quand la feuille de référence est modifiée,
tu remplaces le module3 (j'ai ajouté des Thisworkbook au cas où la macro est déclenchée par un autre classeur actif, ton erreur venait peut-être de là) par :

Public Sub RenommeOnglets()    'à placer dans un module
    Dim i As Long, nS As Long, NomTemp As String
    '    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '    RenommeOnglets = True
    NomTemp = ThisWorkbook.Sheets(1).Range("Y3")
    nS = ThisWorkbook.Sheets.Count
    For i = 1 To nS
        ThisWorkbook.Sheets(i).Name = NomTemp & i
    Next i
    For i = 1 To nS
        ThisWorkbook.Sheets(i).Name = ThisWorkbook.Sheets(i).Range("Y3").Value
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Puis, comparaison de Y3 avec le nom de l'onglet, à mettre dans Feuil1 :

Private Sub Worksheet_Calculate() 'à placer dans Feuil1
    If (Feuil1.Name <> Range("Y3")) Then RenommeOnglets
End Sub

A toi de voir si ça suffit ou s'il est plus sûr de faire de même avec les autres feuilles.
A+

 

[hellle]

Re : renommer onglet par macro, appel impossible

Yeeeepeeeeeeeee !

C'est par-fait, (et la référence directe à l'onglet est effectivement plus simple et logique -dans la macro de la feuille)
Il semble tout à fait suffisant de ne l'introduire que dans la feuille 1.
Il ne me reste qu'à tester sur la version fichier complet, et je ferai un dernier retour.

Mille fois merci de ton partage et de ton travail.

 

[hellle]

Re : renommer onglet par macro, appel impossible

Bonjour, dernier retour un peu tardif.

Juste pour dire que

1- ça fonctionne impecc sur le fichier complet

2- j'ai rajouté un appel à cette macro sur "Before Save", de façon à empecher tout changement manuel intempestif des onglets, et du même coup, protéger toutes les liaisons à ce classeur source.

Encore merci Hippolite !