[ModDate] Pour une feuille précise

Hello tout le monde,

J'ai besoin d'un petit coup de main sur le code suivant :

Public Function ModDate()
    Application.Volatile
    ModDate = Format(FileDateTime(ThisWorkbook.FullName), "DDDD dd MMMM yyyy - hh:nn")
End Function

Ça marche top quand je l'intègre dans l'une de mes cellules de mon fichier. Seulement, je ne voudrais pas renvoyer la date de dernière modification du fichier Excel MAIS de la feuile précise (mettons "Ma Feuille 1") dans laquelle j'intègre ma fonction.

Je me perds un peu avec ActiveWorkShett et les autres :/

Merci par avance !!

Bonjour,

Tu n'enregistres pas une feuille mais le classeur donc tu ne peux pas connaître sur quelle feuille a eue lieu la dernière modification avec cette fonction !

Il te faut utiliser "Workbook_SheetChange()" pour créer un nom invisible (n'apparaît pas dans la boite) et y enregistrer les valeurs voulues.

Dans le module du classeur :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim Nom As Name

    ThisWorkbook.Names.Add "DerModif", Sh.Name & " - " & Target.Address(0, 0) & " - " & Now, False

End Sub

dans un module standard :

Public Function ModDate() As String

    Dim Nom As Name

    Application.Volatile

    On Error Resume Next
    Set Nom = ThisWorkbook.Names("DerModif")
    If Err.Number = 0 Then ModDate = Replace(Right(Nom, Len(Nom) - 1), """", "")

End Function

tu auras alors comme résultat, le nom de la feuille, l'adresse de la cellule ou de la plage de cellules si suppression et la date et l'heure

Hello again Theze

Ces codes sont top et fonctionnent nickel.

Toutes fois, j'ai deux petits soucis :

1. l'update des informations s'effectuent dans les cellules où j'ai collé les fonctions dans toutes les feuilles. En gros, si c'est dans la feuille 2 que je fais une modif', on retrouve les infos également dans la feuille 1. Je voudrais pouvoir dissocier ces résultats afin que chaque feuille puisse afficher la date afférente à sa propre modification.

2. dans quelle partie des codes proposés je peux choisir le formatage des dates ("DDDD dd MMMM yyyy - hh:nn" dans mon cas) ?

Je progresse tout de même énormément grâce à tes précieux conseils, encore merci !

Petit fichier test joint...

Bonjour,

Dans ce cas, il faut créer un Nom par feuille mais il n'est pas possible ou plutôt pas conseillé d'utiliser le nom de l'onglet car trop de risque qu'il soit modifié par l'utilisateur mais plutôt utiliser le nom de son module qui lui ne change jamais quelque soit le nom de l'onglet. Par contre, la fonction ModDate() doit demander un argument, le nom de l'onglet donc, la fonction devient :

Public Function ModDate(NomFeuille As String) As String

    Dim Nom As Name

    Application.Volatile

    On Error Resume Next
    Set Nom = ThisWorkbook.Names(Worksheets(NomFeuille).CodeName)
    If Err.Number = 0 Then ModDate = Replace(Right(Nom, Len(Nom) - 1), """", "")

End Function

La procédure événementielle devient :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    ThisWorkbook.Names.Add Sh.CodeName, Sh.Name & " - " & Target.Address(0, 0) & " - " & Now, False

End Sub

Dim "Nom As Name" est un reste inutile de précédents tests !

La fonction dans les feuilles de calculs est appelée de la façon suivante :

=ModDate(DROITE(CELLULE("nomfichier";A1);NBCAR(CELLULE("nomfichier";A1))-CHERCHE("]";CELLULE("nomfichier";A1))))

Une fois de plus, Theze, c'est parfait !

C'est plus complexe que je l'imaginais mais ça fonctionne au poil

J'ai juste adapté la procédure événementielle (voilà un jargon que je ne maîtrise point encore) afin de n'avoir que les informations qui m'intéressent et au format souhaité :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    ThisWorkbook.Names.Add Sh.CodeName, Format(Now(), "DDDD dd MMMM yyyy - hh:nn"), False

End Sub

Quant à la fonction, j'ai fait sauter le "Dim Nom As Name" :

Public Function ModDate(NomFeuille As String) As String

    Application.Volatile

    On Error Resume Next
    Set Nom = ThisWorkbook.Names(Worksheets(NomFeuille).CodeName)
    If Err.Number = 0 Then ModDate = Replace(Right(Nom, Len(Nom) - 1), """", "")

End Function

Encore merci, je m'éclate avec ces fonctions et ça me donne carrément envie d'encore progresser sur ces sujets.

Si dans la fonction tu as supprimé la variable c’est que tu n’as pas "Option Explicit" en tête de module sinon tu aurais eu un plantage

Dans la fonction, il faut laisser la déclaration de la variable !

C’est dans la procédure événementielle qu’elle est à supprimer car pas utilisée !

Theze a écrit :

Si dans la fonction tu as supprimé la variable c’est que tu n’as pas "Option Explicit" en tête de module sinon tu aurais eu un plantage

Dans la fonction, il faut laisser la déclaration de la variable !

C’est dans la procédure événementielle qu’elle est à supprimer car pas utilisée !

Ok, j'avais mal saisi le propos de ton précédent post

Quoi qu'il en soit, tout roule et le code est complété comme il faut :

Public Function ModDate(NomFeuille As String) As String

    Dim Nom As Name

    Application.Volatile

    On Error Resume Next
    Set Nom = ThisWorkbook.Names(Worksheets(NomFeuille).CodeName)
    If Err.Number = 0 Then ModDate = Replace(Right(Nom, Len(Nom) - 1), """", "")

End Function

Merci encore Theze !

Re !

Je me permets de revenir ici car j'ai un petit problème qui est apparu depuis l'intégration de la macro à mon fichier.

Pour une raison qui m'échappe, mais alors totalement, la possibilité d'annuler une action a disparue !! Ctrl+Z ne fonctionne plus et même la flèche "Retour" en raccourci est inactive.

Bien entendu, cela ne se produit que dans ce classeur, c'est pourquoi j'en déduis que les manipulations ci-dessus y sont pour quelque chose...

En cherchant droite à gauche j'ai cru à un problème du nombre d'annulation permis qui est conditionnée par la base de registre mais cela me paraît fort improbable :/ http://www.bursoft.fr/annulation_excel.htm

Des idées les gens ?

Merci par avance

Bonjour,

Effectivement, c'est la création du Nom qui grise les flèches

Je vais chercher pour quelle raison car il n'y a à priori pas de rapport !

Re,

Cela me rassure quelque peu que le problème vienne bien de la fonction mais c'est effectivement étonnant

Au moins on en apprend tous les jours !

Re,

Effectivement, c'est l'accès au gestionnaire de noms par VBA qui grise les flèches, si le nom est créé manuellement et même modifié manuellement, pas de problème mais créé manuellement puis modifié par VBA, les flèches se grises

Je te propose une autre solution mais elle n'est pas portable de PC à PC puisque les valeurs sont stockées dans la base de registre du PC ou le classeur est utilisé !

La fonction :

Public Function ModDate(NomFeuille As String) As String

    Application.Volatile

    ModDate = GetSetting("SurveillanceClasseur", "DerModif", Worksheets(NomFeuille).CodeName, "")

End Function

Le code dans la module du classeur :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    SaveSetting "SurveillanceClasseur", "DerModif", Sh.CodeName, Sh.Name & " - " & Target.Address(0, 0) & " - " & Now

End Sub

Les valeurs seront stockées dans un dossier créé spécifiquement par MicroSoft et le dossier s'appelle "VB and VBA Program Settings" dont le chemin est :

"Ordinateur\HKEY_CURRENT_USER\Software\VB and VBA Program Settings"

Donc, si tu suis le chemin tu verras une fenêtre qui va ressembler à ça :

Spoiler

Pour y accéder, tu cherches Système Windows--> Exécuter et dans le champ tu entres regedit puis tu valides et tu cliques sur Oui à la fenêtre qui te dit que tu veux accéder à la base de registre.

Sur l'image tu peux voir en bas à gauche le dossier "DerModif" qui a été créé avec son dossier parent "SurveillanceClasseur" qui lui se trouve dans "VB and VBA Program Settings" et en haut à droite, les valeurs enregistrées concernant les différentes feuilles du classeur (tu vois celle de mon classeur test)

Si tu veux tout supprimer, voici le code qui va remettre le registre comme il était avant :

Sub Supprimer()

    DeleteSetting "SurveillanceClasseur"

End Sub

Saches que si tu ne va pas farfouiller ailleurs dans le registre, il n'y a aucun risque !

Sinon, il reste la solution de la feuille cachée ou l'écriture dans un module mais, c'est plus compliqué !

Re !

Merci pour cette alternative Effectivement, cela complique un peu mon problème car c'est justement un fichier partagé avec plusieurs utilisateurs via Cloud et serveur local.

Et du coup, que se passe-t-il si un utilisateur l'ouvre sur un Mac par exemple ?

Autre cas de figure, l'utilisateur n'a pas de droits d'admin sur sa machine, la fonction sera-t-elle bloquée pour créer les données dans la BDR ?

Hum... je vais faire des tests et voir ce qu'il en ressort

Donc, maintenant, avec une feuille cachée, le code devient un peu plus complexe :

code du module du classeur :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim Fe As Worksheet
    Dim Plage As Range
    Dim Cel As Range

    If Sh.Name = "Cachée" Then Exit Sub

    On Error Resume Next
    Set Fe = Worksheets("Cachée")

    'suspend les événements
    Application.EnableEvents = False

    'si la feuille n'existe pas encore, la crée
    If Err.Number <> 0 Then

        Set Fe = Worksheets.Add
        Fe.Name = "Cachée"
        Fe.Visible = xlSheetVeryHidden 'n'apparaît pas dans la boîte !

    End If

    'défini la plage sur la colonne A (noms des feuilles ou plutôt du nom de leurs modules)
    With Fe: Set Plage = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With

    'recherche le nom...
    Set Cel = Plage.Find(Sh.CodeName, , xlValues, xlWhole)

    'si il existe, inscrit les valeurs
    If Not Cel Is Nothing Then

        Cel.Offset(, 1).Value = Sh.Name & " - " & Target.Address(0, 0) & " - " & Now '<-- ici, adapte ce que tu veux avoir comme renseignements !

    'si non, ajoute le nom et les renseignements
    Else

        Worksheets("Cachée").Cells(Plage.Rows.Count + 1, 1).Value = Sh.CodeName
        Worksheets("Cachée").Cells(Plage.Rows.Count + 1, 2).Value = Sh.Name & " - " & Target.Address(0, 0) & " - " & Now '<-- ici aussi, il te faut adapter !

    End If

    'rétabli
    Application.EnableEvents = True

End Sub

et de la fonction :

Public Function ModDate(NomFeuille As String) As String

    Dim Fe As Worksheet
    Dim Plage As Range
    Dim Cel As Range

    Application.Volatile

    'contrôle si la feuille existe, si non, inscrit 0 !
    On Error Resume Next
    Set Fe = Worksheets("Cachée")

    If Err.Number <> 0 Then

        ModDate = "0"
        Exit Function

    End If

    'ici, la feuille existe donc, recherche le nom de la feuille (nom du module)
    'et retourne la valeur située en colonne B au droit du nom
    With Fe: Set Plage = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)): End With

    Set Cel = Plage.Find(Worksheets(NomFeuille).CodeName, , xlValues, xlWhole)

    If Not Cel Is Nothing Then ModDate = Cel.Offset(, 1).Value Else ModDate = "0"

End Function

Pour afficher la feuille pour contrôle :

Sub Afficher()

    Dim Fe As Worksheet

    On Error Resume Next
    Set Fe = Worksheets("Cachée")

    If Err.Number <> 0 Then

        MsgBox "La feuille cachée n'existe pas encore !"

    Else

        Worksheets("Cachée").Visible = xlSheetVisible

    End If

End Sub

Hello Theze !

Désolé pour le retour tardif (je n'étais pas devant un écran pendant quelques jours).

J'ai machiné les codes proposés et autant dire, so far, so good !!

Je n'ai pas noté de problématique donc cela a bien l'air de répondre à ma requête (je me permettrais de revenir vers toi le cas échéant).

Et en effet, le code est plus complexe (j’avoue m'y perdre dedans mais j'ai pu l'adapter selon mes besoins).

J'ai du mal à évaluer la somme de travail que cela t'a prit donc mille mercis pour ce travail dantesque !!

J'ai créé un petit fichier pour que d'autres utilisateurs puissent éventuellement s'y référer

Encore bravo et merci !!!

Bonjour,

Content de t'avoir aidé