Event Change Macro - Improve my Code?

can you code an Event Change macro to only run if certain cells are changed in a worksheet rather than any cell

Not as far as I know. The change event handler will fire for every change. You have to monitor the cells you are interested in manually. You already have the code you need ... perhaps you just need to move it to the start of the module.

One thing that I noticed is that the msgbox "authorizations duplicated" is inside your loops. That means that if you have multiple instances you will get multiple msgboxes.

But, the msgbox is only to inform you of a duplication, the extra boxes aren't needed.

You could set a variable and use that to trigger one message, no matter how many duplicates there are

Please Login or Register  to view this content.

Originally Posted by puppet_master2501

... some ... tuning on my Event Change code ...

Using mikerickson's solution and shortening the duplicate code ("If Not Intersect ...", "For ... Each ... Next"), it could look like this:

Please Login or Register  to view this content.

... but, how the "change" procedure can be started at all, if the worksheet is protected and you can not enter anything into its cells before you unlock/unprotect the worksheet ? What "mechanism" allows you to perform the "change" procedure ? Do you have any cells there that are not blocked ?

Originally Posted by porucha vevrku

Using mikerickson's solution and shortening the duplicate code ("If Not Intersect ...", "For ... Each ... Next"), it could look like this:

Please Login or Register  to view this content.

... but, how the "change" procedure can be started at all, if the worksheet is protected and you can not enter anything into its cells before you unlock/unprotect the worksheet ? What "mechanism" allows you to perform the "change" procedure ? Do you have any cells there that are not blocked ?

Hi porucha vevrku - many thanks for the detailed code.

In the worksheet only certain cells are protected - including a range permission restricting users ability to input into cells unless they are named in the permissions list.

The code is a safety and audit measure to check whom is inputting into the permission controlled range so that - if for any reason someone other than those named could populate into it, the system creates a stamp so it can be investigated.


In regards to your code I will go away and test - I have not seen

Please Login or Register  to view this content.

before!


Thanks all for your input - if you have any more or alternative code it would be interesting to see the approaches!


EDIT: To check should the above code have a

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

lines above the errorhandler: code?

EDIT: Also I have note come across defining variables like this before:

Please Login or Register  to view this content.

- could you please explain this?

EDIT: Lastly think

Please Login or Register  to view this content.

needs to be changed to

Please Login or Register  to view this content.

for the duplication check to work correctly.

PASS123 - Copy.xlsm

Thanks for the rep.