Jul 06 2020 05:17 AM
Jul 06 2020 05:17 AM
I'm wondering if there is a way for Excel to tell me if a user has made changes to any entry in a row in a workbook?
For example, if someone changes any cell value in a particular row in a workbook, I want a particular cell in that row to tell me who changed it, based on the user information logged in Excel.
|Value||Value||Person who make change in any row|
|Row 1||Any change||Any change||User log in will show in this cell if they change Row1|
|Row 2||Any change||Any change||User log in will show in this cell if they change Row2|
|Row 3||Any change||Any change||User log in will show in this cell if they change Row3|
Jul 06 2020 07:58 AM
@mitchellbourke__ I see 2 potential solutions for you: VBA or Track Changes
VBA - using VBA you can detect changes on a sheet and update a particular cell using one of a number of calls that will return the user name (e.g. activesheet.range("D2").value = application.UserName). Note these calls will return the user name that Excel is registered under not necessarily the use doing it (hopefully they aren't sharing PWs or logins or MS office registrations) or a name that matches a particular list (e.g. my registration/login credentials are different than what a particular company/group may normally communicate to me using). If you are familiar with VBA and do a quick search on how to detect changes on a sheet I think you can write this pretty quickly.
Track Changes - Excel has track changes capability. You may have to enable the menu item as legacy functionality. There are some tricks to make it working right including locking the sheet and setting the duration to maximum value. In this fashion you can see who changed what and when and such. I learned about this when I did a webinar about how to make Excel worksheet FDApart11 compliant (https://www.upiq.com/trainings/livewebinar/3295/excel-spreadsheets-ensuring-data-integrity-and-21-cf...). But instead of their name being in that particular cell it will show up in a change sheet or as a comment in the cell. Although this isn't the exact output you wanted it may be a more secure way to track changes and you don't need to write VBA code.