Hi guys, at my workplace I created and are in charge of maintenance of several worksheets (saved in a share drive) my colleagues use throughout the day for various work purposes. Sometimes they have problems with the workbooks that I have no idea how they made happen. So I found a track change macro that I like that logs the changes in a separate .txt doc. The downside is because each person has its own personalized workbook, that means I have a .txt docs for each of them. This is part of the code that designate the .txt file where the changes are logged in:
What I would like to do is save everything in one separate workbook where each sheet logs the co-worker's changes in their workbooks, instead of having multiple .txt files. For example if I have 20 employee workbooks, the log workbook would have 20 sheets logging each of their changes. This way all the information is available in one place without having to go search it, and it would also be faster to clear up old data. I tried playing around with it, looking in this board and elsewhere for an answer but a lot of the other post were to put the changes within the same workbook, but I need it to be in a separate one due to the multiple coworker's workbooks.
Also if its not too complicate is there a way to show in the ThisWorkbook_SheetChange module which macros are used? The workbooks I set up have various macros for the purposes of emails, IM's, reports, and document so if an error/problem happens from a macro I'd like to be able to review it in the log rather than back trace it from the cell changes or having to go to their computer and see whats going on. Below is part of the same Track changes code that I currently have in the SheetChange module:
If you guys need me to post the full code let me know. I didn't do so because I found it in a different website. All credit for this code goes to JLLatham. At work we are running Excel 2010 in Windows 7. Any help with this is greatly appreciated!
Code:
Const recordFile = "\\SHAREDRIVE\Folder1\Folder2\useLog.txt"What I would like to do is save everything in one separate workbook where each sheet logs the co-worker's changes in their workbooks, instead of having multiple .txt files. For example if I have 20 employee workbooks, the log workbook would have 20 sheets logging each of their changes. This way all the information is available in one place without having to go search it, and it would also be faster to clear up old data. I tried playing around with it, looking in this board and elsewhere for an answer but a lot of the other post were to put the changes within the same workbook, but I need it to be in a separate one due to the multiple coworker's workbooks.
Also if its not too complicate is there a way to show in the ThisWorkbook_SheetChange module which macros are used? The workbooks I set up have various macros for the purposes of emails, IM's, reports, and document so if an error/problem happens from a macro I'd like to be able to review it in the log rather than back trace it from the cell changes or having to go to their computer and see whats going on. Below is part of the same Track changes code that I currently have in the SheetChange module:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim fileBuffer As Integer
fileBuffer = FreeFile()
On Error Resume Next ' in case network resource not available
Open recordFile For Append As #fileBuffer
Print #fileBuffer, Sh.Name & "!" & Target.Address & " altered by " & Application.UserNameIf you guys need me to post the full code let me know. I didn't do so because I found it in a different website. All credit for this code goes to JLLatham. At work we are running Excel 2010 in Windows 7. Any help with this is greatly appreciated!
