Is there a wy to write an event to capture protection change

[JohnG]

I would like to be able to capture the protection being changed on a sheet
and grey out the menu option of my add-in accordingly. Is there a way in VBA
to write your own event?

 

[Mark O'Brien]

No, unless you're prompting the user to protect the sheet.

 

[Corticus]

Hi,

If ActiveSheet.Protect Contents:=True
or
If ActiveSheet.Protect DrawingObjects:=True
or
If ActiveSheet.Protect Scenarios:=True

I think should work, depending on what you're checking for...

HTH,
Corticus

edit:
okay if Mark says no, then I guess not, sorry

This message was edited by Corticus on 2002-10-17 15:58

 

[kskinne]

inserting this code would allow him to check whether or not protection is turned on and then perform a task based on whether protection is on or off.

however this still leaves him w/ no event to trigger the code and make it run. since there is not protect event, is there anything else that would work for him?
This message was edited by kskinne on 2002-10-17 16:11

 

[Damon Ostrander]

Hi John,

The only way I can think of in the absence of such an event is that you could replace the built-in Protect menu option with one of your own. The downside: this would "trap" manual protection, but not protection via VBA code, so if the user is determined to get around it he/she can.

Damon

 

[JohnG]

Actualy the main reason for this is to be able to disable my add-in should the protection be enabled as it formats cells.
At the moment it has to check and reply with a message that the sheet is protected.
You can see the normal Excel format options go grey with protectin on so its in there somewhere guys (and girls), don't let Uncle Bill's boys beat us.

 

[Mark O'Brien]

That functionality to grey out the boxes is in there, we're not disputing that. It's just that there's no event available to trap it and we can't create our own events or else there'd be madness in the world.

Thanks Corticus. I simply go by the rule of saying "no it can't be done" to everything, if I'm proved wrong then the solution provider is a genius.

 

[Corticus]

Hmmm...

Does it have to be triggered by protection change?

Couldn't you trigger the event more than necessary, say with Worksheet_Change(), then test to see if protection has been changed.

You could set a variable when the workbook is opened to the protection value (True/False) and using other events as triggers, check for a change.

Maybe?

(just trying to be a genious! )

HTH,
Corticus

 

[JohnG]

Are there's the rub.

Changing the protection in a sheet does not trigger the sheetChange event, thanks uncle Bill.

 

[kskinne]

not really what you probably want, but as a last resort perhaps you should trigger this code by a worksheet_change event or a worksheet_calculate event - that way if the protection was changed, as soon as the user does anything causing the worksheet to change or re-calculate, it would check the protection property, and run your code. like i said, it wouldn't be the ideal solution, but until you can come up with an alternative, it would do the job, albeit in a round-about fashion.

kevin