Protect/Unprotect sheet change detection -vba

[shannen]

Situation:
I have 5 sheets in my workbook.

Sheet1
Sheet2
Sheet3
Sheet4
DetectionSheet

This is my question:

Is there a possibility that when a user CHANGE the protection (protect/unprotect) of a Sheet(1-4) content, the NAME of the sheet will be throw and catch(it will be written in a cell) by the DetectionSheet

Example:

I change the protection of Sheet1 and Sheet2

"DetectionSheet" have [A1]Sheet1, [A2]Sheet2

AND when I remove the protection(Unprotect) it will be deleted in the "DetectionSheet".

[Debraj Roy]

Not completely what you actually wants.. but will give you an idea, in which way you need to go..

Place this code in DetectionSheet

Please Login or Register  to view this content.

will modify with more validation if its in correct direction..

[Andy Pope]

You can hook into the Tools > Protection > Protect sheet, event

standard code module

Please Login or Register  to view this content.

clas Class1 module

Please Login or Register  to view this content.

[Debraj Roy]

Hi Andy..

in which trigger / event.. the value will reflect.. in DetectionSheet!!

[Andy Pope]

The class object catches the clicking of the menu button. It then uses OnTime to run the LogProtection routine. This allows the dialog to appear and the macro will not execute until the use clears the dialog either by cancel or setting/unsetting protection.

The activesheets index is used to output information on the DetectionSheet, using the index as the row position.

To test you will need to run the Main macro then alter sheet protection on a sheet.