Automaticall disable Protection menu

[Guest]

Hi,

hopefully this is a simple query. I have built a large excel model, which
is password protected. The model can be unlocked using a macro (which
requires the inputting of the password).

I want to prevent the user from unlocking individual sheets through the
Tools-Protection menu, i.e. they can only unlock the model by running the
macro. I know i can disable the Protection menu by using a macro, but what i
want is for the Protection menu to be available to the user at all times,
except when my model is the active workbook. is there a way to
automatatically disable the Protection menu when my model worksheet is
activated, then automatically turn it back on when another worksheet is
activated?

Any help is appreciated!

Martin

 

[Guest]

Hi dunny_budgie

You should start off having the sheets protected with a password (eg "fish",
why not?)

Your macro will ask for a password from the users.
If they get it wrong, the code will end (exit sub) and nothing changes.

If they get it right, then you will use this code to unprotect the sheets:

sheets("sheet1").unprotect password:="fish"

This way they can either use your macro to unprotect the sheets, or they can
unprotect them manually but they would have to know the password "fish".

That should move things along for you I hope

 

[Guest]

On second thoughts maybe it is more complicated than that.

Whatever code you have thought up to disable the protection menu, how about
putting in inside the worksheet activation event procedure for this sheet:

Private Sub Worksheet_Activate()
'<-----here
End Sub

You would then need a deactivation procedure as well which would run just
before you activated any other sheet.

Private Sub Worksheet_Deactivate()
'<-----here
End Sub

HTH

 

[Guest]

--

Regards,

Halim

 

[Robert Bruce]

Hi,

hopefully this is a simple query. I have built a large excel model,
which is password protected. The model can be unlocked using a macro
(which requires the inputting of the password).

Use a different password in your code to unprotect the sheets to the one
that you expect the users to input.

Rob

 

[Bob Phillips]

If you must use code, maybe


Private Sub Workbook_Activate()
Application.CommandBars("Tools").Controls("Protection").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Tools").Controls("Protection").Enabled = True
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Tools").Controls("Protection").Enabled = True
End Sub

Private Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protection").Enabled = False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob, that was exactly what i was after.

martin

 

[Guest]

Hi,

Sorry for missing my past post ...
Code to unable or dissable protection menu like :
Function DisablProt(Enabled As Boolean)
'Disabling Tools>Protection>.. commandbar :
Application.CommandBars.Item("tools"). _
FindControl(ID:=30029).Enabled = Enabled
End Function

Sub CallDisablProt()
DisablProt False
'change to true if enabling
End Sub

Thks