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

JohnG

Board Regular
Joined
Feb 18, 2002
Messages
165
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?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239

ADVERTISEMENT

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

Board Regular
Joined
Feb 18, 2002
Messages
165
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530

ADVERTISEMENT

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. :biggrin:

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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

Board Regular
Joined
Feb 18, 2002
Messages
165
Are there's the rub.

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

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top