Souvent, lorsque plusieurs personnes utilisent un même fichier Excel, il est intéressant de savoir qui a accédé au fichier dernièrement afin de pouvoir connaître à qui se référer pour mieux comprendre les dernières modifications qui ont été effectuées au fichier. Voici un exemple de journal de bord, plus communément appelé un log des utilisateurs :
Première étape de la création d’un log des utilisateurs
Pour créer ce genre de log, il suffit d’ajouter un onglet et quelques lignes de programmation VBA à votre fichier. Premièrement, ajoutez un onglet à votre fichier et renommez-le « Log ». Dans la cellule A1, inscrivez « Usager » et dans la cellule B1 « Date ». Deuxièmement, ouvrez l’éditeur VBA en appuyant sur ALT+F11. À gauche de votre écran, apparaîtera la liste des onglets du fichier. À la fin de cette liste, il y a l’item « ThisWorkbook », double-cliquez sur celui-ci.
Deuxième étape de la création d’un log des utilisateurs
Ensuite, dans la fenêtre de droite, sélectionnez« Workbook » et dans la deuxième liste déroulante, sélectionnez « Open ». Deux lignes de code apparaîteront automatiquement comme le démontre l’image suivante :
Troisième étape de la création d’un log des utilisateurs
Copiez finalement les lignes de programmation suivantes et collez-les entre les lignes créées :
Vous souhaitez créer et personnaliser des outils de gestion automatisés dans Excel pour mieux répondre aux besoins spécifiques de votre entreprise ? Apprenez à les développer et les présenter avec des interfaces conviviales pour les usagers en suivant nos formations en programmation de macros VBA. |
|---|
Ces étapes complétées, le nom de l’usager et la date seront automatiquement inscrits dans l’onglet « Log » à l’ouverture du fichier.
Formation complémentaire
Pour une introduction à la programmation avec le langage VBA, qui permet de sauver du temps et de réduire les risques d’erreurs en automatisant certaines tâches et certains processus, afin de développer des solutions robustes dans Excel, suivez la formation Excel – VBA (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Bonjour,
Je n’avais jamais pensé mettre un log en place sur un fichier excel ! Bonne idée, surtout dans un contexte de partage de fichier.
Par contre ce petit bout de code gagne à être simplifié et optimisé : pourquoi recourir à une structure conditionnelle quand on peut l’éviter (if … then … else … end if)
Voici ce qui peut être fait :
Worksheets(« Log »).Select
‘ début de la modification
Columns(“A”).Select
Selection.Find(Empty).Activate
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
‘ fin de la modification
Worksheets(strOngletInitial).Select
Résultat : de 7 à 4 lignes et une structure “complexe” en moins.
Il est peut-être possible d’encore faire mieux… et VBA pour Excel peut grandement vous accompagner pour ça !
Emmanuel
Bonjour,
j’utilise une technique similaire, sauf qu’au lieu d’être stockée dans le classeur, l’information m’est adressée par mail. ça fait un peu “espion” mais ça me permet d’être alerter dès qu’il y a une mise à jour sensible… Cdt
Bonjour,
Je ne sais pas si c’est de l’espionnage mais recevoir un mail permet d’être informé immediatement quand un fichier est mis à jours sans avoir à aller ouvrir le document de manière périodique.
Pourriez vous nous faire partager votre technique?
Cordialement,
Emmanuel Arthur
Ping : Log des derniers utilisateurs d'un fichier Exce...
Bonjour,
Je trouve cette idée vraiment pratique et un Log pourrait vraiment m’aider dans mon travail, malheureusement j’ai une version Excel francophone. Serait-il possible d’avoir la programmation en français ?
Bonjour Poon,
Le fait que vous ayez la version française d’Excel n’est pas une barrière à l’intégration de cette macro dans vos fichiers. Peu importe la version d’Excel, la programmation VBA est toujours en anglais donc vous pouvez aller de l’avant dans l’intégration de cette macro à vos fichiers.
Bonne journée,
Formation VBA? Bring it on!
J avais fait ce genre de log pour voir qui utilisait un de mes fichiers. Mais maintenant ce que j aimerai ce serait de le faire mais sur un fichier de consultation qui s ouvre en lecture seule, mais je n arrive pas a trouver une astuce car étant en lecture seule les utilisateurs ne peuvent pas apporter de modifications. Une idée peut être ? Sans avoir recours a un autre fichier qui lui ne serait pas en lecture seule ?
En tout cas votre site est super 🙂
Bonsoir sloy,
Si je comprends bien, vous voudriez un log pour voir les usagers qui consultent un fichier en lecture seule sans avoir le droit de le modifier. J’ai ajouté quelques lignes de programmation au code VBA initial, voici le code modifié :
Dim strOngletInitial As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
strOngletInitial = ActiveSheet.Name
Worksheets(“Log”).Select
If Range(“A1”).Offset(1, 0).Value = “” Then
Range(“A1”).Offset(1, 0).Value = Application.UserName
Range(“A1”).Offset(1, 1).Value = Now()
Else
Range(“A1”).End(xlDown).Offset(1, 0).Value = Application.UserName
Range(“A1”).End(xlDown).Offset(0, 1).Value = Now()
End If
Worksheets(strOngletInitial).Select
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Ces lignes de codes devraient répondre à votre besoin.
Bonne soirée.
Rebonsoir,
Comme plusieurs l’ont déjà expérimenté, malheureusement le site converti très les guillemets de façon incorrecte. Donc, si vous utilisez le code que j’ai mis dans ma réponse à sloy, assurez-vous de convertir tous les guillemets en guillemets anglais (Voir l’image du code dans l’article initial.
Désolé de ce petit problème technique.
Bonne soirée.
Bonjour,
Merci pour votre réponse
mais je ne sais pas si ça va marcher
Je ne vois pas l’instruction qui dit de permettre le fichier en écriture, et de plus vu que ce sont des fichiers qui sont dans des dossiers qui sont gérés par notr service informatique sur un serveur citrix, je pense que si la macro essaye de s’éxécuter à l’ouverture, il y aura un message du genre “ce fichier est utilisé par un autre utilisateur” ou bien qu’on n’ a pas les droits suffisants pour le faire passer en écriture ?
Toujours de très bon conseils sur ce blog! Merci.
Si on veut tracer uniquement les mises à jour il faut utiliser l’évènement BeforeSave à la place de Open
Bonjour
Est-ce que ce log nécessite que l’utilisateur qui ouvre le fichier active les macros à l’ouverture du fichier ?
Cordialement
Bonjour,
Non, l’utilisateur n’a pas à activer quoique ce soit.
Au plaisir,
Sophie
Bonjour, je relance ce post puisque je me retrouve dans le cas où je dois utiliser ce code VBA.
Quand j’ouvre mon fichier, il m’indique bien mon nom et prénom même cas pour une de mes collègues. Le problème c’est quand j’ouvre le fichier après elle, je n’aperçois pas son nom dans la liste alors qu’elle a bel et bien ouvert le fichier.
Avez-vous une idée sur le problème ?
Bonjour Nma,
La première idée qui me vient en tête est : Est-ce que votre collègue a enregistré le fichier avant de le fermer et vous le remettre ?
J’attends votre réponse avant d’investiguer davantage,
Bonne journée,
Martin
Bonjour,
Je me suis rendu compte que le code marchait uniquement quand la personne sauvegarde le fichier, en fait ce que je voudrais faire c’est afficher également les personnes qui consultent mon fichier, mais qui n’y touche pas. C’est pour effectuer un contrôle quotidien afin de savoir si les personnes se renseignent sur les informations que contiennent le fichier. Je vous avoue que je suis un peu bloqué…
En attente d’une réponse de votre part,
Bonne journée.
Nma
BOnjour Nma,
La seule solution que je vois est de créer le log dans un fichier externe. On ouvre ce fichier, on inscrit le nouvel utilisateur et on ferme ce fichier en le sauvegardant. Donc, même si l’usager ne sauvegarde pas le fichier courant, le log, lui, est sauvegardé.
Bonjour,
Avant tout un grand merci à ceux qui mettent à disposition leurs connaissances.
J’essai la version du code de Martin et voici quelques remarques et demandes :
1. Est-ce possible de me dire comment protéger l’onglet “Log” d’éventuelles suppressions ?
2. D’autre part le code cesse de fonctionner à un moment :
Range(“A1”).End(xlDown).Offset(1, 0).Value = Application.UserName me donne :
Stéphane LOUVEL 10/11/2015 11:40 > sur la première ligne
Stéphane LOUVEL > et seulement une seconde ligne incomplète et puis plus rien si j’enregistre à nouveau.
Je cherche.. et je modifie ces lignes pour corriger mon Pb :
Else
Range(“A2”).End(xlDown).Offset(1, 0).Value = Application.UserName
Range(“A2”).End(xlDown).Offset(0, 1).Value = Now()
Ce qui donne ce que j’attends : C’est mieux !
Stéphane LOUVEL 10/11/2015 11:40
Stéphane LOUVEL
Stéphane LOUVEL 10/11/2015 11:42
HUGO Victor 10/11/2015 11:43
Stéphane LOUVEL 10/11/2015 11:43
3. Est-ce possible d’ajouter la/les cellules modifiées : Onglet “novembre 2015” cellule “A57” à la suite de “HUGO Victor 10/11/2015 11:43” ?
Merci d’avance,
Stéphane.
Bonjour Louvel,
En réponse à vos questions:
1- Si vous voulez évitez que des usagers efface cette feuille, vous devriez la mettre en mode TRÈS CACHÉ. Pour ce faire, ajouter l’Instruction Worksheets(“Log”).visible = xlsheetvisible avant le Select et l’instruction Worksheets(“Log”).visible = xlsheetveryhidden avant de quitter la procédure. De cette manière, cette feuille sera carrément invisible aux usagers.
2- Selon moi, l’instruction 2 du ELSE devrait plutôt se lire comme suit: Range(« A2 »).End(xlDown).Offset(1, 1).Value = Now(). Cela devrait régler le problème.
3- Pour l’affichage du mois et de l’année, il va falloir suivre les instructions suivantes:
Ajouter une variable comme Dim StrMois as String
Juste avant le IF, introduisez un SELECT CASE. Le nouveau code de la procédure devrait donc être:
Dim strOngletInitial As String
Dim strMois as String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
strOngletInitial = ActiveSheet.Name
Worksheets(« Log »).visible = xlsheetvisible
Worksheets(« Log »).Select
SELECT CASE Month(Date)
Case 1
strMois = “Janvier – ”
Case 2
strMois = “Février – ”
Case 3
strMois = “Mars – ”
Case 4
strMois = “Avril – ”
Case 5
strMois = “Mai – ”
Case 6
strMois = “Juin – ”
Case 7
strMois = “Juillet – ”
Case 8
strMois = “Août – ”
Case 9
strMois = “Septembre- ”
Case 10
strMois = “Octobre – ”
Case 11
strMois = “Novembre – ”
Case 12
strMois = “Décembre – ”
END SELECT
strMois = strMois & Year(Date)
If Range(« A1 »).Offset(1, 0).Value = « » Then
Range(« A1 »).Offset(1, 0).Value = Application.UserName
Range(« A1 »).Offset(1, 1).Value = Now()
Range(« A1 »).Offset(1, 2).Value = strMois
Else
Range(« A1 »).End(xlDown).Offset(1, 0).Value = Application.UserName
Range(« A1 »).End(xlDown).Offset(1, 1).Value = Now()
Range(« A1 »).End(xlDown).Offset(1, 2).Value = strMois
End If
Worksheets(strOngletInitial).Select
Worksheets(« Log »).visible = xlsheetveryhidden
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Cela devrait faire le travail.
Et n’oubliez pas de remplacer les ” par de vrais guillemets.
Bonsoir à tous,
J’ai copié le code d’Emmanuel mais à l’ouverture du fichier, VBA s’ouvre et m’indique “Instruction incorrecte à l’exterieur d’un bloc de type”. Mes cours de VBA ne sont pas si vieux, et pourtant je vous avoue en avoir oublié une grande partie.
Si vous pouviez m’aider.
Je vous remercie
Pierre
Private Sub Workbook_Open()
Application.DisplayFullScreen = True
DimstrOngletInitial As String
Application.ScreenUpdating = False
strOngletInitial = ActiveSheet.Name
Worksheets(«Log»).Select
Columns(«A»).Select
Selection.Find(Empty).Activate
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Value = Now
Worksheets(strOngletInitial).Select
Application.ScreenUpdating = True
End Sub
Bonsoir
je suis kendy
j’ai un projet de creer un login sur excel en macros stp j’aimerais avoir de l’aide stp
d’aide bien detailler
Bonjour à vous,
j’ai mis le code et cela fonctionne très bien quand j’ouvre le fichier, le seule problème est que nous sommes plusieurs personnes à utiliser ce fichier .
je m’explique : une liste de personne avec leur nom et un mot de passe tous différent ( eux ont le droit à l’enregistrement ) et un nom avec un mot de passe celui ci à le droit que en lecture seule.
Avez vous une solution !
Merci à vous.
Amicalement.
Manu,
Vous devriez ajouter un set de commandes comme suit:
Dim sh as worksheet
if Application.UserName = “UsagerSansDroit” Then
For each sh in Worksheets
sh.Protect UserInterfaceOnly:=True
next sh
end if
Ces commandes vont empêcher cet usager de faire des changements dans le fichier.
Voilà,
Francis
Bonjour Francis,
Un grand merci pour votre réponse ultra rapide.
Je vient vers vous car j’ai encore une question : pour le login des personnes ayant le doit en lecture seule pas de souci, par contre avec tout les codes plus haut sur cette page je ne vois plus vraiment quoi mettre.
Je veux juste savoir qui ouvre ce fichier sachant qu’il y a environ 30 personnes sur une liste cachée sur une autre feuille ces personnes ont les droit à l’enregistrement et les autres ont un seule et même login mais en lecture seule. Est il possible de s’avoir qui ouvre ce fichier avec la date , l’heure et le nom de la personne qui à ouvert le fichier. J’en demande beaucoup mais je suis dans l’impasse.
Amicalement.