activation macro si changement VALEUR celluleRésolu

Question

Bonjour à toutes et tous, la macro suivante vérifie le nom des onglets du classeur quand il y a changement dans une cellule de la colonne 3 Le problème est que la valeur de ces cellules est gérée via une formule liée a un autre classeur et la macro "lit" la formule (qui est invariable) et non le résultat (qui est variable).Donc, pas d'activation de la macro. Y a-t-il moyen de faire comprendre à cette satanée macro que c'est la valeur de la cellule qu'il faut prendre en compte? Je sais qu'il y a une alternative, c'est de cibler le dossier source dans lequel les changements sont effectués, mais le problème est que le dossier de classeurs sera dupliqué chaque année avec (forcement) un changement de nom. Donc le chemin de la source sera à modifier chaque fois dans la macro et ce n'est pas moi qui serai en charge de dupliquer le dossier. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 3 Then Exit Sub Dim Feuille As Worksheet For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name <> "Horaire" Then If Feuille.Name <> Feuille.Range("R1") Then Feuille.Name = Feuille.Range("R1") End If Next End Sub merci pour votre aide cordialement Thierry

foliv57 · Accepted Answer

Code à placer dans dans ThisWorkbook Private Sub Workbook_SheetActivate(ByVal Sh As Object) RenameSheet Sh End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) RenameSheet Sh End Sub Private Sub RenameSheet(Sh As Worksheet) Dim newName As String newName = Sh.Range("A1").Value If (Sh.Name <> newName) Then Sh.Name = newName End If End Sub Comme ca, tous les onglets controleront leur nom sur activation ou sur recalcul. J'ai ajouté un controle du chamgement de nom pour ne pas le réaffecter si celui-ci n'a pas changé. Il est possible, dans la Sub RenameSheet, de controler le nom si certaines feuilles ne doivent pas se mettre à jour. Par exemple Private Sub RenameSheet(Sh As Worksheet) If (Sh.Name <> "Toto") And (Sh.Name <> "Tata") Then Dim newName As String newName = Sh.Range("A1").Value If (Sh.Name <> newName) Then Sh.Name = newName End If End if End Sub Dans ce cas, les feuilles "Toto" et "Tata" ne controlent pas leur nom.

foliv57 · Answer

Bonjour,

Feuille.Range("R1").Value

Devrait vous donner la valeur

charette63 · Answer

Bonjour foliv57

merci pour ta participation, 
le problème est le même, "R1" est géré lui aussi par une liaison

ce que je cherche à faire, c'est que le nom de l'onglet d'une feuille change dés que "R1" change
"R1" est géré via liaison dans un listing d'un autre classeur
Pour le moment, j'ai ceci:

Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("r1").Value
End Sub

la modification du nom de l'onglet s'effectue à l'ouverture de la feuille mais j'ai rencontré deux problèmes

1 je n'ai pas que quelques feuilles à gérer et il arrive régulièrement qu'il ne faille pas ouvrir la feuille de suite aprés le changement sur le listing et ensuite, c'est parfois galère de retrouver l'onglet à l'ancien nom de la feuille

2 il est arrivé lors de changements dans le listing de perdre des liaisons (Ref#), une solution était de renseigner la liaison non avec le nom de l'onglet mais avec le codename de la feuille, mais apparement, ce n'est pas possible

Il faudrait donc que le changement du nom de l'onglet s'effectue au changement de valeur d'une des cellules ou ligne.

J'ai bien essayé ceci, mais ça coince (ah, j'oubliais, je suis novice)


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = (R1) Then
ActiveSheet.Name = Range("r1").Value
End Sub

merci pour ton aide

foliv57 · Answer

Ok, je voie mieux le problème.

Votre souci est que l'évènement "Worksheet_Change" n'est pas lancé lors d'une mise à jour de liaison ou lors d'un calcul.

Elle est uniquement lancée sur modification directe dans la feuille.

Il vous faut donc plutot ceci

'Exécuté lors de l'activation de la feuille
Private Sub Worksheet_Activate()
    Me.Name = Me.Range("A1").Value
End Sub

'Exécuté lors du recalcul de la feuille
'et donc lors de la mise à jour de la liaison
Private Sub Worksheet_Calculate()
    Me.Name = Me.Range("A1").Value
End Sub

charette63 · Answer

Rebonjour foliv57

le premier code que tu me proposes, est celui que j'ai déja (cf mon second post)

le deuxième que tu me proposes fonctionne à merveille

Sans avoir l'air d'abuser,est-il possible de faire fonctionner cette macro au départ de workbook ou d'un module,
j'ai effectivement un peu plus de 50 feuilles x 12 mois à faire la modif, dans l'optique d'une reponse positive, cela m'en fera 50 fois mois.

D'ores et déja, un grand merci

cordialement

Thierry

cs_Jack · Answer

Salut

"est-il possible de faire fonctionner cette macro au départ de workbook ou d'un module"
Je pense qu'il suffit de demander à Excel de faire un recalcul (avec la simple commande Calculate) dans Worksheet_Activate pour que cet évènement se déclenche.

Vala
Jack, MVP VB
NB : Je ne répondrai pas aux messages privés

Le savoir est la seule matière qui s'accroit quand on la partage (Socrate)

foliv57 · Answer

J'ai oublié de préciser qu'avec mon dernier code, l'exemple précédent (Worksheet_Calculate) peut être supprimé de la feuille.

Sinon le travail est fait deux fois.

charette63 · Answer

Merci de ta participation Jack, le code de foliv57 fonctionne du feu de dieu

Comme cité ci dessus cher foliv, ta solution est bien celle que je cherchais. Magnifique!!

Mille mercis

Cordialement

Thierry

charette63 · Answer

Zut zut et rezut

la macro fonctionne très bien lorsque le second classeur est ouvert (pour rappel, j'ai un listing dans un premier classeur qui influe sur plusieurs autres classeurs). Mais lorsque j'ouvre un autre classeur,bien que le nom de l'onglet ai changé, je perd des liaisons (REF#).
Si le nom de l'onglet a changé, c'est que la macro fonctionne, mais que les mises à jour des liaisons sont plus rapide (???) 
Y-a-t-il moyen de retarder la mise à jour des liaisons de quelques secondes afin qu'elle "remarque" le changement de nom?

les liaisons ne sont pas ordonnées par macro, mais par fonction

j'espere que mon explication est suffisement claire

Cordialement

Thierry

foliv57 · Answer

Oula,

Dans quel classeur avez vous des #Ref ?

On parle bien de #Ref sur des formules entre feuilles du même classeur ?

Il faut que j'arrive à me faire un exemple pour tester le problème.

Il me faudrait des noms de classeur bien précis ansi que les formules et l'ordre d'ouverture qui provoque les #Ref ?

Par exemple :
+ Le classeur "Listing.xls" contient des noms d'onglet dans les cellules "A1" à "A2" de la feuille "LesNoms"

+ Le classeur "monClasseur.xls" contient 2 feuilles. 
Feuil1 avec la formule "=[Listing.xls]LesNoms!A1" dans la cellule "R1"
Feuil2 avec la formule "=[Listing.xls]LesNoms!A2" dans la cellule "R1"

+ J'ouvre les deux classeurs, je change la valeur de "A1" dans la feuille "LesNoms" de "Listing.xls" et la feuille "Feuil1" de "monClasseur.xls" se renome.

(Jusque la je pense ne pas me tromper mais après je ne vois pas quelles formules provoquent des #REf)

J'imagine que vous avez plusieurs classeurs et plusieurs formules qui posent problème comme vous l'avez expliqué plus haut, mais on va déjà faire un exemple ciblé.

charette63 · Answer

Je vais tâcher d'être clair

un dossier comprend:

un premier classeur Annuel nommé "renseignement"
   -une première feuille appelée "récapitulatif"
   -X feuilles appelées par le nom de l'employé via la valeur d'une cellule (A1)(sujet de ma demande initiale)
   dans la première feuille un listing des employés (source des noms qui déterminent le nom des onglets)

douze classeurs mensuels nommés "horaire01", "horaire02", ... (les 12 mois de l'année)
  chaque classeur comprend:
   -une première feuille appelée "horaire commun"
   -X feuilles appelées par le nom de l'employé via la valeur d'une cellule (A1) (sujet de ma demande initiale)  
  
les liaisons que je perd, sont celles: 

 - entre la première feuille du deuxieme classeur ("horaire commun") (sur les lignes du nom de l'employés repris sous l'onglet) et la feuille "X" du premier classeur (X=nom de l'employé)

 -entre la feuille "X" du deuxième classeur et la feuille "X" du premier classeur (X étant toujours le nom de l'employé)

SI un employé s'appelle Durant, sa feuille perso du premier classeur s'appellera Durant ainsi que sa feuille perso du second classeur.

La liaison s'appellera donc : '[renseignement.xls]Durant'!n° cell et devient : '[renseignement.xls]REF#'!n° cell

C'est bien, à mon sens, du au nom de la feuille qui a changée le classeur 

Au risque de me repeter, lorsque un classeur "horaire commun" est ouvert, rien ne se perd, mais si j'ouvre, aprés le changement de nom dans le listing, un autre classeur là, les liens ont perdu le lien.

J'espere que l'explication est suffisement claire

cordialement

charette63 · Answer

j'ai crée deux petits fichiers excel qui illustre bien mon problème, mais je ne vois pas comment les poster dans un message

Pas de pièce jointe sur ce site?

cordialement

Thierry

foliv57 · Answer

Ok, avec cette exemple je devrai pouvoir reproduire le problème.

Non malheureusement on ne peut pas joindre de fichier (sauf erreur de ma part).

Je n'aurai peut etre plus le temps de regarder ce soir, mais je fais ca des que possible.

foliv57 · Answer

Par contre je peux déjà vous donner l'idée que j'ai en tête comme ca vous pourrez tester ca.

Si le problème vient bien du renomage des feuilles dans le classeur "renseignement" lorsque le classeur mensuel est fermé (ce qui parait logique vu que le classeur mensuel n'est pas informé de ce changement lorsqu'il est fermé), une solution possible serait de rempacer les liens statiques, dans le classeur mensuel, par des liens dynamiques.

Je m'explique. Dans le fichier mensuel, dans une des feuilles X, si la cellule A1 contient bien le nom X, remplacez les formules du type 

='[renseignement.xls]X'!n° cell

par des formule du type 

=INDIRECT("'[renseignement.xls]" & $A$1 & "'!n° cell")

La fonction INDIRECT permet de faire référence à une cellule ou une plage de cellules par une chaine de caractères. On peut donc créer dynamiquement le lien vers la bonne feuille par concaténation.

charette63 · Answer

vraiment très sympa de te creuser les méninges pour mon problème,
je pense qu'il serais plus simple de te faire parvenir le petit dossier (2 fichiers de trois feuilles)
ne serais-tu pas par hasard inscrit sur un autre forum dans lequel il y à moyen de faire parvenir des pièces jointes?

encore, un tout grand merci

charette63 · Answer

Bonjour foliv,

La nuit porte peut-être conseil,

vu que j'ai déjà des boutons spécifiques à chaque classeur placé dans la barre d'outil, boutons qui actives des macros placées dans un "module" XLA, 
vu que le lien que je perd est du au changement de nom de l'onglet et que le nom de l'onglet est celui du lien,
n'est il pas possible d'automatiser, via une boite de dialogue, l'action que je fais actuellement lorsque je perd un lien ,à savoir dans la barre excel ,"édition"--"remplacer" (REF# par "le nom de l'onglet"). J'ai essayer de le faire via l'éditeur mais l'action ne s'enregistre pas.

Une idée peut-être

cordialement

charette63 · Answer

Voici une macro créée via l&#8217;éditeur,

Est-il possible de crée une boite de dialogue qui s&#8217;ouvre lorsque « #REF » est notée sur la feuille active. Dans cette boite noter le nouveau nom de la liaison (nom de l&#8217;onglet) et qui prendrait la place de « Brnould M » dans la macro ?

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 11/05/2011 par thierry
'
    With ActiveWindow
        .Top = 5.5
        .Left = 34.75
    End With
    
    Cells.Select
    Selection.Replace What:="#REF", Replacement:="Brnould M", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    
    
End Sub


Cordialement

Thierry

foliv57 · Answer

Sur ce principe, voici une procédure qui parcourt les feuilles du classeur et remplace les REF# par le nom de la feuille.

Public Sub CorrigeFormules()
    
    On Error Resume Next
    Dim feuille As Worksheet
    For Each feuille In ThisWorkbook.Worksheets
        feuille.Cells.Replace What:="REF#", Replacement:=feuille.Name, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
    Next
    Application.Calculate
    
End Sub

Pour mon autre idée (celle avec la formule INDIRECT) malheureusement la fonction ne fonctionne que si le classeur source est ouvert.

Donc la seule autre facon de faire, en dehors du remplacement des REF#, qui me vient à l'esprit est d'installer cette macro complémentaire morefunc et d'utiliser la fonction INDIRECT.EXT qui permet de faire un lien dynamique vers un classeur fermé.

En remplacant les formules problématiques par des formules du type

=INDIRECT.EXT("'CheminFichier[NomDuClasseur]" & $A$1 & "'!n° cell") 

$A$1 étant la cellule qui contient le nom de l'onglet

charette63 · Answer

bonjour foliv,

ton code "corrigeformule" est une petite merveille, il faudra juste que j'exclue de la liste des feuille à vérifier la "feuil1",ça, ça devrait aller.
J'aurai de nouveau besoin d'aide pour l'adapter à ma "feuil1", est-il possible de l'adapter pour une plage de cellule vers une feuille: exemple, ("H10:F11")de la feuille1 vers feuil2.Name, les cellules("H12:F13")de la feuille1 vers feuil3.Name.

merci

charette63 · Answer

ou mieux encore, 

dans ma feuil1, deux lignes consecutives sont "consacrées" par employé, j'ai des liaison dans des cellules de ces lignes qui sont également perdues (je te rassure, avec le même nom que celles des autres feuilles)
Dans une colonne de cette feuille j'ai également le nom de l'employé est-il possible de ne faire qu'un code?

exemple: 
la ligne 3 et 4 sont consacrées à Durant M,  les cellules ("ak3:AM4") perdent leur lien, et le nom Durant M et repris en cellule AN3
la ligne 5 et 6 sont consacrées à Dupont P,  les cellules ("ak5:AM6") perdent leur lien, et le nom Durant M et repris en cellule AN5
....etc etc...

foliv57 · Answer

La procédure de correction deviendrait ceci
Public Sub CorrigeFormules()
    
    On Error Resume Next
    Dim feuille As Worksheet
    Dim ligne As Long
    Dim ligneFin As Long
    
    For Each feuille In ThisWorkbook.Worksheets
        
        'Pour feuil1
        If feuille.Name = "Feuil1" Then
        
            'Calcul de la dernière ligne du tableau.
            'Cette formule recherche la première cellule
            'vide vers le bas en partant de la cellule
            'AK3. Il faut que la colonne AK soit remplie
            'pour chaque ligne du tableau. Si ce n'est pas
            'la cas il faudra remplacer AK par une colonne pleine.
            'S'il n'y en a aucune, il faudra trouver un autre moyen de
            'déterminer la dernière ligne
            ligneFin = feuille.Range("AK3").End(xlDown).Row
                    
            'Pour chaque employé (de la ligne 3 à la
            'fin du tableau en sautant 2 lignes à chaque fois)
            For ligne = 3 To ligneFin Step 2
                
                'On effectue un recherche/remplace dans la plage AK-AM de la ligne courante et de la ligne
                'suivante. On remplace REF# par la valeur de la colonne AN de la ligne courante
                feuille.Range("AK" & ligne & ":AM" & ligne + 1).Replace What:="REF#", _
                        Replacement:=feuille.Range("AN" & ligne).Value, LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
                
            Next
        
        Else 'Pour toutes les feuilles autres que feuil1
        
            feuille.Cells.Replace What:="REF#", Replacement:=feuille.Name, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
        End If
    Next
    
    Application.Calculate
End Sub

Le seul souci est le calcul de la dernière ligne du tableau dans feuil1. Voir explication dans le commentaire.

charette63 · Answer

Purée, quel travail!!!

Pour info, la colonne AK est pleine ... de formules et liaisons, mais pas forcement de valeurs.

Je teste ça en début de soirée

encore pour info, la dernière ligne concernée est la ligne 70, il y a peut-etre moyen de stopper la macro à cette ligne?

merci beaucoup foliv

foliv57 · Answer

Si la dernière ligne est toujours la ligne 70, il vous suffit de remplacer

ligneFin = feuille.Range("AK3").End(xlDown).Row

Par
ligneFin = 70

Et le tout est joué

charette63 · Answer

Mon cher foliv,

c'est une merveille, tout fonctionne parfaitement.
Les cellules avec formule, sont bien considérées par la macro comme non vide (et c'est tant mieux)
C'est un plaisir de travailler avec toi (ben oui, je n'ai fait que de te regarder )
Mille et un mercis

cordialement

Thierry

foliv57 · Answer

Mais de rien.

Au plaisir de faire plaisir.

Bonne continuation.

Activation macro si changement VALEUR cellule

25 réponses

Votre réponse

Discussions similaires