audit log VBA code not capturing IF formula result

**Alister Davis** · 04-11-2018, 05:52 AM

I have an audit log VBA code, to capture changes from several worksheets into one worksheet called "LRLog".

This only writes value changes. Formatting changes are not required to be captured.

The VBA code works fine, but does NOT capture changes in those cells which are determined by IF formula based on data input in other cells.

Users will make changes in columns C, D, E, F, and H, with IF formula in columns A and G showing a result, dependent on the data input in columns D, E, and F.
However, currently, any changes to cols A or G (because of the outcome of formulae in col A or G) are not captured in the audit log, as attached:
(yellow blocks in the attached Audit log are what is expected should be displayed).

The following VBA has been used in the sheets where changes will take place.
------------------------------------------

Please Login or Register  to view this content.

------------------------------------

Can anyone help with the VBA code so that any changes to cols A or G (because of the outcome of formulae in col A or G) are also captured in the audit log?

I would also like to include good error handling in this project if possible.

**Norie** · 04-11-2018, 05:55 AM

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

PS When posting code can you please use CODE tags?

**Alister Davis** · 04-12-2018, 02:00 AM

Apologies for non-Code tag usage, but I have no experience with VBA and have been doing a Copy & Paste of VBA code, using the web page below as a starter, and adapting other VBA as I find and where I think they may fit:
Mr Excel web page - "VBA Code to create an audit trail in Excel 2010"

In the interim, I have reduced the number of audit columns to the following.

Who
Sheet
Cell(s)
Changed FROM
Changed TO
Date
Time

Attached is a sample workbook, with Sheets "LR", "AD", and "LRLog" (the audit log);
LR = (basically a test page to see if whatever is typed on this particular sheet is captured in the audit log);
AD = is a section of the worksheet that I intend using.

**Norie** · 04-12-2018, 04:34 AM

The Change event of a worksheet is not triggered when a value changes due to the result of a formula.

What formula(s) do you have in column A & G?

**Keebellah** · 04-12-2018, 04:52 AM

Hi, I found this tracking log on Ozgrid's site (is mentioned in the code), works for me, you will probably have to tailor it to your needs but is does also mention formula resuls.

Please Login or Register  to view this content.

I suggest you visit that site for further information.

**Alister Davis** · 04-12-2018, 05:13 AM

For example:
In Cell A7: =IF(G7="REPEALED","REPEALED",L7)

In Cell G7: =
IF(AND(L7="Compliance",D7="R",E7="R",F7="R"),"REPEALED",
IF(AND(L7="Compliance",D7=1,E7="",F7=""),"Compliant",
IF(AND(L7="Compliance",D7="",E7=1,F7=""),"Semi-compliant",
IF(AND(L7="Compliance",D7="",E7="",(OR(F7=1,F7=""))),"NON-COMPLIANT",

IF(AND(L7="Must",D7="R",E7="R",F7="R"),"REPEALED",
IF(AND(L7="Must",D7="",E7="",F7=""),"CRITICAL IF NON-Compliant",
IF(AND(L7="Must",D7=1,E7="",F7=""),"Compliant",
IF(AND(L7="Must",D7="",E7="",(OR(F7=1,F7=""))),"CRITICAL IF NON-Compliant",

IF(AND(L7="Heading",D7="",E7="",F7=""),"Heading",
IF(AND(L7="Info",D7="",E7="",F7=""),"Info",

IF(AND(L7="Info",D7="R",E7="R",F7="R"),"REPEALED",
IF(AND(L7="Heading",D7="R",E7="R",F7="R"),"REPEALED",

IF(NOT(OR(AND(L7="Heading",D7="R",E7="R",F7="R"),AND(L7="Heading",D7="",E7="",F7=""))=TRUE),"ERROR",
IF(NOT(OR(AND(L7="Info",D7="R",E7="R",F7="R"),AND(L7="Info",D7="",E7="",F7=""))=TRUE),"ERROR",

"CHECK VALUE & INSERT EXPLANATION"))))))))))))))

**Alister Davis** · 04-13-2018, 09:11 AM

Hi Keebellah, that does not work for me.
The initial code works better for me, save for the problem in not capturing changes in the IF formula.

audit log VBA code not capturing IF formula result

LinkBack

Thread Tools

Rate This Thread

Display

audit log VBA code not capturing IF formula result

Re: audit log VBA code not capturing IF formula result

Re: audit log VBA code not capturing IF formula result

Re: audit log VBA code not capturing IF formula result

Re: audit log VBA code not capturing IF formula result

Re: audit log VBA code not capturing IF formula result

Re: audit log VBA code not capturing IF formula result

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Unique counter based on the location code and time of an audit

VBA code for audit trail issue

Assistance with Audit code...

[SOLVED] Code result wrong but formula correct result

VBA code for Audit schedule email-weekly

[SOLVED] Web Query - Submitting Data and Capturing Result

stock audit & bar code scanner

Bookmarks

Bookmarks

Posting Permissions