SelectionChange event not firing

[dfriederichs]

Hi all,

First post on the forum, so 'Hi' to you all! I've used your forum in lots of cases to help me address issues in VBA programming, but now I've encountered one that is quite persistent. I'm on Excel 2019 and am looking to create an audit trail for capturing changes to a planning file I manage at work.

Capture.JPG

Above is the audit trail I am looking for. The problem I am having is that the "Previous Value" is not captured due to a Worksheet_SelectionChange not firing. Please find the code below that is present in the target worksheet.

Please Login or Register  to view this content.

As said previously, the Worksheet_Change event does fire, but the Previous Value is empty as the Worksheet_SelectionChange event does not fire. I've tested and excluded Application.EnableEvents was set to False. Does anyone have any idea where I should be looking?

Best regards,
Don

[BellyGas]

I think the event is firing but your variable isn't holding the target value.

Read this to learn all about variable scope: https://stackoverflow.com/questions/...ariable-in-vba

[romperstomper]

If you change one cell, the PreviousValues will not be an array, and your Change event code won't work. Equally, if you did change more than one value, it will be a 2 dimensional array, not 1D, so your code will fail. You won't know that either way though because you stuck On Error Resume Next at the top of it, so nothing will appear to be wrong.

Personally, my preference would be to use just the Change event, store the Target.Value in your variable, disable events, then use Application.Undo to remove the changes and then test the old value(s) as appropriate. You can then reinstate the changes and reset events.

[dfriederichs]

Hi all,

Thanks for your quick replies. Scoping shouldn't be the issue (as far as I can tell now), as I have declared the PreviousValues outside of the events, so it can be used by both Change and SelectionChange events. I had it working yesterday and it could detect all changes in an array and make note of that in the AuditTrail worksheet, which is a functionality I'd like to preserve as I am often finding myself adjusting multiple cells in the same time (proprietary information, so unable to share). Hoping you can have a look and help me out, thanks again!

Best regards,
Don

[romperstomper]

Respectfully, there is no way the code you posted ever worked. Target(c).Value and PreviousValues(c) make no sense at all if c is a Range object, in addition to the errors I mentioned previously.

You could do something like this:

Please Login or Register  to view this content.

[dfriederichs]

Hi Rorya,

Thank you for looking into the code! I had to switch the PreviousValues and a.Value columns to have the macro insert in the proper column, but other than that it works like a charm!!!

Best regards,
Don