Hello everyone,

I have a worksheet which works great except i wish to distribute it and so
want to protect it with a password. so far so easy but the next day when i
want to amend it and redistribute it i need to unprotect it again.

Dim strSharePwd as string

If ActiveSheet.Protect Then
'strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If


Ive got an ActiveXcontrol on the spreadsheet which should ask for the
password to be supplied either to protect or unprotect using the following
code but instead it only wishes to protect. My if statement doesnt seem to
funnel towards unprotecting.

Can anyone help?

With thanks,

Amit

Hi,

Is there an apostrophy in from on the 'strSharePwd for a reason?

Greg


DowningDevelopments wrote:
Hello everyone,

I have a worksheet which works great except i wish to distribute it and so
want to protect it with a password. so far so easy but the next day when i
want to amend it and redistribute it i need to unprotect it again.

Dim strSharePwd as string

If ActiveSheet.Protect Then
'strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If


Ive got an ActiveXcontrol on the spreadsheet which should ask for the
password to be supplied either to protect or unprotect using the following
code but instead it only wishes to protect. My if statement doesnt seem to
funnel towards unprotecting.

Can anyone help?

With thanks,

Amit

Hi Downing,

Try to use this one :

Dim strSharePwd as string
'this "Un&protect Sheet..." is case sensitives ...
If CommandBars("protection"). _
FindControl(ID:=893).Caption = "Un&protect Sheet..." Then
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If

That will check wether sheet is protect or not by finding out the caption of
commandbars ...
--

Regards,

Halim


"DowningDevelopments" wrote:

Hello everyone,

I have a worksheet which works great except i wish to distribute it and so
want to protect it with a password. so far so easy but the next day when i
want to amend it and redistribute it i need to unprotect it again.

Dim strSharePwd as string

If ActiveSheet.Protect Then
'strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If


Ive got an ActiveXcontrol on the spreadsheet which should ask for the
password to be supplied either to protect or unprotect using the following
code but instead it only wishes to protect. My if statement doesnt seem to
funnel towards unprotecting.

Can anyone help?

With thanks,

Amit

You code currently calls the Method to protect a worksheet (without a
password). It does not have a return value, so your If test evaluates to
false and you always get the "Enter password To protect" branch of code
executed.
It does not test if the WS is protected.

For that you one/more of these Or'd together
ActiveSheet.ProtectContents=True
..ProtectDrawingObjects
..ProtectScenarios

If ActiveSheet.ProtectContents = True Then
'strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If

NickHK

"DowningDevelopments" wrote
in message ...
Hello everyone,

I have a worksheet which works great except i wish to distribute it and so
want to protect it with a password. so far so easy but the next day when i
want to amend it and redistribute it i need to unprotect it again.

Dim strSharePwd as string

If ActiveSheet.Protect Then
'strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If


Ive got an ActiveXcontrol on the spreadsheet which should ask for the
password to be supplied either to protect or unprotect using the following
code but instead it only wishes to protect. My if statement doesnt seem to
funnel towards unprotecting.

Can anyone help?

With thanks,

Amit

Thanks guys, Halim's suggestions works great for me

"Halim" wrote:

Hi Downing,

Try to use this one :

Dim strSharePwd as string
'this "Un&protect Sheet..." is case sensitives ...
If CommandBars("protection"). _
FindControl(ID:=893).Caption = "Un&protect Sheet..." Then
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If

That will check wether sheet is protect or not by finding out the caption of
commandbars ...
--

Regards,

Halim


"DowningDevelopments" wrote:

Hello everyone,

I have a worksheet which works great except i wish to distribute it and so
want to protect it with a password. so far so easy but the next day when i
want to amend it and redistribute it i need to unprotect it again.

Dim strSharePwd as string

If ActiveSheet.Protect Then
'strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Unprotect Password:=strSharePwd
Else
strSharePwd = InputBox("Enter password To protect")
ActiveSheet.Protect Password:=strSharePwd
End If


Ive got an ActiveXcontrol on the spreadsheet which should ask for the
password to be supplied either to protect or unprotect using the following
code but instead it only wishes to protect. My if statement doesnt seem to
funnel towards unprotecting.

Can anyone help?

With thanks,

Amit