Problem updating audit trail

Hi,

I am new to the forum and I am looking for some help please.

I am building an application to manage project resources in Excel 2003 using VBA. I will be sharing the sheet with managers and allowing them to make changes but I want to have an audit trail of what they have changed.

On one sheet I have a table of projects consisting of 10 columns and n rows. I have a 'Projects' range defined on the 1st column and a 'ProjAuditInfo' range defined on the 10th column.

I have been able to get the auditing to work up to a point using the worksheet change event with an offset from the target cell of (0, 9) which updates Column 10. See below for the code I have been using :

Please Login or Register  to view this content.

This works fine to track if any single cells have been changed but only in the Projects range (Col 1).

My problem though is that I want to extend the target range to include additional contiguous columns in the table (not just column 1) but I still want to have the audit log updated in col 10 (ProjAuditInfo).

I have tried several things to try to get this to work and I haven't been able to find an answer to this problem in any of the forums. All of the worksheet change solutions seem to assume that a single column is being checked.

So I need some help to come up with the code that will take the target cell from anywhere in the expanded range (let's call this "ExpProjects") and go over to the same row on the "ProjAuditInfo" range and update this cell only with the relevant audit info.

I would be very grateful for any help with this problem,

Many thanks,


gtol

Please take a moment to read the forum rules and edit your post to include code tags.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button).

Dom

Thanks for adding the tags.

Maybe:

Please Login or Register  to view this content.

Dom

No worries. Just another point but I prefer to use Environ("Username") rather than Application.UserName as people have a habit of changing the Excel user details to some meaningless rubbish.

Dom