SUMMARY:
If you have network and separate user accounts then I'd just enable "Track
Changes" feature (#2 ADVANCED).
If everyone is logged in or using same user account then I'd develop a
user+password sign-in macro that appears OnLoad (each time workbook is
opened) (#3 EXPERT/DEVELOPER).
MORE INFORMATION
------------------------
There is no easy way to enforce change tracking. This is specially true if
file is accessed by different people using same user account (such as from
same PC with no requirement to login as different users). Below are the
options I can think of with my comments. Others are welcome to comment on
these #1 - #4 or add other solutions.
#1 (INTERMEDIATE): Voluntary change tracking
1. Create new worksheet named "TRACKING" as first worksheet in the workbook.
2. Write instructions how you want all users to enter their usage information.
3. Always close workbook after moving to "Tracking" worksheet so it always
appears when workbook is opened.
4. Trust (and pray) all users follow the instructions. Good luck (You'll
need it).
#2: (ADVANCED): "Track Changes" feature (requires network and user accounts)
Read help or related info on the "Change Tracking" or "Track Changes"
feature (Excel 2003 and later). If you can share your Excel workbook then you
can enable "Track Changes" feature.
1. Save file on a network fileshare or SharePoint server
2. Open file with Excel, then set Review->Share Workbook (Excel 2007)
3. Enable Review->Track Changes and related settings you need/want.
Change tracking gives fine grain detail on who changed what cells and when.
Wonderful stuff if your network and users are set up to meet the sharing
requirements.
#3 (EXPERT/DEVELOPER): As mentioned by Bob I, If everyone is opening this
file with same user account then you'll need to make a macro (or more likely
develop Excel VBA code) to pop-up a custom sign-in dialog that adds a row to
a protected change tracking form. I did this very successfully for an Access
database application to implement record-level change tracking (about 8 years
ago).
TIP: You might be able to programmatically retrieve username and machinename
from the OS environmental variables. look up COMPUTERNAME and USERNAME and
environment for more info.
#4 (MASTER): NTFS File System journal entries
Another possible solution is to track access and modifications at file
system level. Windows Server 2003 and later, Vista, (and XP with
modifications) can keep a log of file activity. It really helps to have
different user accounts accessing, otherwise you are limited to just getting
date/time entry each time someone accesses or modifies the file. Read OS File
system help for help implementing this type of tracking.
Hope this helps. Sorry I can't provide a fully functioning sample or code
fragments to help further.
