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

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 :-)

--
Allllen


"dunny_budgie" wrote:

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

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
--
Allllen


"dunny_budgie" wrote:

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

--

Regards,

Halim


"dunny_budgie" wrote:

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

Hi,

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

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

Thks
--

Regards,

Halim


"Halim" wrote:


--

Regards,

Halim


"dunny_budgie" wrote:

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

Roedd <<dunny_budgie wedi ysgrifennu:

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

If you must use code, maybe


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

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

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

Private Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protect ion").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)

"dunny_budgie" wrote in message
...
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

Thanks Bob, that was exactly what i was after.

martin



"Bob Phillips" wrote:

If you must use code, maybe


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

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

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

Private Sub Workbook_Open()
Application.CommandBars("Tools").Controls("Protect ion").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)

"dunny_budgie" wrote in message
...
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