VBA Challenge

Challenge

WOW #4.22: There are two small functions which I just can't get my Excel workbooks and Word documents to perform, and I thought you might be able to help if you have time.

1. Workbook function one: file access log.

I seek a script to log the current time and application username in a 'spare' empty macro module, as ' comment or rem lines.

In addition, and most ideal but perhaps too sophisticated, would be to insert the username and time in a specified field on one worksheet within an Excel workbook, but only when that individual worksheet has been modified, regardless of the other worksheets in the XLS file. (This would save relying on users to add this information after an update, in a situation where different users each have exclusive responsibility for maintaining their own single worksheet within a shared workbook.)

2. Workbook function two: ensure changes are saved on file close, but ONLY if changes have been made.

Solution

Dear Sirs,

Attached you'll find a partial solution of your challenge. Partial because of the fact that one of the conditions of your task is "to log the current time and application username in a spare' empty macro module, as comment or Rem lines" and another is to be able to use workbooks in shared mode. But these two conditions contradict each other - you cannot create/write to macro module in shared mode. I think that you need to change the specs of your challenge - write not to macro module but to (hidden) specail log worksheet.

In attached zip file you'll find LogRecord.xls which can be saved as an add-in (.xla), installed and used to open and handle several files simultraneously. Three class modules are sent for you be able to recreate add-in manually. The code isn't documented - if you find it suitable for your VBA challenge I'll supply it with comments.

THNX for your great WOW,
Kind regards,
Shamil

Click do download logrcd.zip (24153 bytes).