Activation macro si changement VALEUR cellule [Résolu]

charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 9 mai 2011 à 22:13 - Dernière réponse : foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention
- 11 mai 2011 à 18:54
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
Afficher la suite 

Votre réponse

25 réponses

Meilleure réponse
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 13:41
3
Merci
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.

Merci foliv57 3

Avec quelques mots c'est encore mieux Ajouter un commentaire

Codes Sources a aidé 93 internautes ce mois-ci

Commenter la réponse de foliv57
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 08:33
0
Merci
Bonjour,

Feuille.Range("R1").Value

Devrait vous donner la valeur
Commenter la réponse de foliv57
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 10:13
0
Merci
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
Commenter la réponse de charette63
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 11:39
0
Merci
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
Commenter la réponse de foliv57
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 12:20
0
Merci
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
Commenter la réponse de charette63
cs_Jack 14010 Messages postés samedi 29 décembre 2001Date d'inscription 28 août 2015 Dernière intervention - 10 mai 2011 à 13:41
0
Merci
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)
Commenter la réponse de cs_Jack
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 13:47
0
Merci
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.
Commenter la réponse de foliv57
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 13:55
0
Merci
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
Commenter la réponse de charette63
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 15:41
0
Merci
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
Commenter la réponse de charette63
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 16:47
0
Merci
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é.
Commenter la réponse de foliv57
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 19:57
0
Merci
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
Commenter la réponse de charette63
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 20:51
0
Merci
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
Commenter la réponse de charette63
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 21:04
0
Merci
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.
Commenter la réponse de foliv57
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 10 mai 2011 à 22:04
0
Merci
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.
Commenter la réponse de foliv57
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 10 mai 2011 à 22:22
0
Merci
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
Commenter la réponse de charette63
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 11 mai 2011 à 09:19
0
Merci
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
Commenter la réponse de charette63
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 11 mai 2011 à 13:01
0
Merci
Voici une macro créée via l’éditeur,

Est-il possible de crée une boite de dialogue qui s’ouvre lorsque « #REF » est notée sur la feuille active. Dans cette boite noter le nouveau nom de la liaison (nom de l’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
Commenter la réponse de charette63
foliv57 423 Messages postés vendredi 17 novembre 2006Date d'inscription 15 juillet 2014 Dernière intervention - 11 mai 2011 à 13:22
0
Merci
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
Commenter la réponse de foliv57
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 11 mai 2011 à 14:01
0
Merci
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
Commenter la réponse de charette63
charette63 91 Messages postés vendredi 2 avril 2004Date d'inscription 19 mai 2011 Dernière intervention - 11 mai 2011 à 14:16
0
Merci
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...
Commenter la réponse de charette63

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.