Simple password function - unprotecting sheets
 

Hello,
I have a problem where I have 1 protected cell on a sheet.
It is at the top of the sheet and it is for a 'supervisor'
to enter his name to say that he has checked the sheet.
Only the supervisor has the password to unprotect the
sheet so he can enter data into that protected cell.
Unfortunately, sometimes the people filling out the sheet
are wanting to insert extra rows, which I want them to be
able to do, but they can't because the sheet is protected.
So - I tried putting a command button on the sheet that
would unprotect the sheet, insert the desired number of
rows, then protect the sheet again
eg. {I don't mind that they can see the password in here
at the moment}

Dim Row_Count As Integer
Dim password
password = "checked"
ActiveSheet.Unprotect password
Row_Count = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset
(Row_Count - 1, 0)).Select
Selection.EntireRow.Insert
ActiveSheet.Protect password, True, True, True

Unfortunately, when I try this, I get the following error:
"Unprotect method of worksheet class failed"

I can guess why - password.

Can anyone suggest as alternative way of having the sheet
unprotected so that rows can be inserted, yet that cell
still protected - even by a simple password for that
single cell?

Simple password function - unprotecting sheets
 

Peter

works for me as is (unless I make a point of making the password wrong)

Slight modification:

Dim Row_Count As Integer
Dim password
password = "checked"
ActiveSheet.Unprotect password
Row_Count = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Row_Count - 1,
0)).EntireRow.Insert
ActiveSheet.Protect password, True, True, True

You could try using the UserInterfaceOnly option for protecting the sheet
.... but I guess there's an underlying problem so this might not help.

Regards

Trevor


"Peter Hill" wrote in message
...
Hello,
I have a problem where I have 1 protected cell on a sheet.
It is at the top of the sheet and it is for a 'supervisor'
to enter his name to say that he has checked the sheet.
Only the supervisor has the password to unprotect the
sheet so he can enter data into that protected cell.
Unfortunately, sometimes the people filling out the sheet
are wanting to insert extra rows, which I want them to be
able to do, but they can't because the sheet is protected.
So - I tried putting a command button on the sheet that
would unprotect the sheet, insert the desired number of
rows, then protect the sheet again
eg. {I don't mind that they can see the password in here
at the moment}

Dim Row_Count As Integer
Dim password
password = "checked"
ActiveSheet.Unprotect password
Row_Count = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset
(Row_Count - 1, 0)).Select
Selection.EntireRow.Insert
ActiveSheet.Protect password, True, True, True

Unfortunately, when I try this, I get the following error:
"Unprotect method of worksheet class failed"

I can guess why - password.

Can anyone suggest as alternative way of having the sheet
unprotected so that rows can be inserted, yet that cell
still protected - even by a simple password for that
single cell?

Simple password function - unprotecting sheets
 

Peter,

Didn't test your code, but it looks clean.

Maybe it's your use of password. Password is a reserved word in Excel
and could be causing some confusion. Suggest you use something like

Dim pswrd as String

pswrd = "checked"
ActiveSheet.Unprotect pswrd
.......
ActiveSheet.Protect pswrd

--
sb
"Peter Hill" wrote in message
...
Hello,
I have a problem where I have 1 protected cell on a sheet.
It is at the top of the sheet and it is for a 'supervisor'
to enter his name to say that he has checked the sheet.
Only the supervisor has the password to unprotect the
sheet so he can enter data into that protected cell.
Unfortunately, sometimes the people filling out the sheet
are wanting to insert extra rows, which I want them to be
able to do, but they can't because the sheet is protected.
So - I tried putting a command button on the sheet that
would unprotect the sheet, insert the desired number of
rows, then protect the sheet again
eg. {I don't mind that they can see the password in here
at the moment}

Dim Row_Count As Integer
Dim password
password = "checked"
ActiveSheet.Unprotect password
Row_Count = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset
(Row_Count - 1, 0)).Select
Selection.EntireRow.Insert
ActiveSheet.Protect password, True, True, True

Unfortunately, when I try this, I get the following error:
"Unprotect method of worksheet class failed"

I can guess why - password.

Can anyone suggest as alternative way of having the sheet
unprotected so that rows can be inserted, yet that cell
still protected - even by a simple password for that
single cell?

Simple password function - unprotecting sheets
 

Thanks for your replies guys.
If the sheet is unprotected originally, it works the first
time but not the second (ie. after it is protected from
code).
If the sheet is protected, it does not work at all,
stating the same error.
The password for protecting/unprotecting the sheet is the
same as quoted in the code but that part seems to be the
problem.
I know it is setting the password correctly because after
it runs successfully the first time, the password required
is that in the code.

Is there a permissions issue somewhere in here?

-----Original Message-----
Peter,

Didn't test your code, but it looks clean.

Maybe it's your use of password. Password is a reserved
word in Excel
and could be causing some confusion. Suggest you use
something like

Dim pswrd as String

pswrd = "checked"
ActiveSheet.Unprotect pswrd
.......
ActiveSheet.Protect pswrd

--
sb
"Peter Hill" wrote in
message
...
Hello,
I have a problem where I have 1 protected cell on a
sheet.
It is at the top of the sheet and it is for
a 'supervisor'
to enter his name to say that he has checked the sheet.
Only the supervisor has the password to unprotect the
sheet so he can enter data into that protected cell.
Unfortunately, sometimes the people filling out the
sheet
are wanting to insert extra rows, which I want them to
be
able to do, but they can't because the sheet is
protected.
So - I tried putting a command button on the sheet that
would unprotect the sheet, insert the desired number of
rows, then protect the sheet again
eg. {I don't mind that they can see the password in here
at the moment}

Dim Row_Count As Integer
Dim password
password = "checked"
ActiveSheet.Unprotect password
Row_Count = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset
(Row_Count - 1, 0)).Select
Selection.EntireRow.Insert
ActiveSheet.Protect password, True, True, True

Unfortunately, when I try this, I get the following
error:
"Unprotect method of worksheet class failed"

I can guess why - password.

Can anyone suggest as alternative way of having the
sheet
unprotected so that rows can be inserted, yet that cell
still protected - even by a simple password for that
single cell?


.

Simple password function - unprotecting sheets
 

I've just posted a similar question and found the answer
on a different forum. Here is what worked for me.

If running the code from an active control, make sure that
the take focus on click property is set to false.

If that is not the problem add the following line before
the unprotect line in the code:
ActiveCell.Activate

Worked for me! Good Luck!


-----Original Message-----
Thanks for your replies guys.
If the sheet is unprotected originally, it works the
first
time but not the second (ie. after it is protected from
code).
If the sheet is protected, it does not work at all,
stating the same error.
The password for protecting/unprotecting the sheet is the
same as quoted in the code but that part seems to be the
problem.
I know it is setting the password correctly because after
it runs successfully the first time, the password
required
is that in the code.

Is there a permissions issue somewhere in here?

-----Original Message-----
Peter,

Didn't test your code, but it looks clean.

Maybe it's your use of password. Password is a reserved
word in Excel
and could be causing some confusion. Suggest you use
something like

Dim pswrd as String

pswrd = "checked"
ActiveSheet.Unprotect pswrd
.......
ActiveSheet.Protect pswrd

--
sb
"Peter Hill" wrote in
message
...
Hello,
I have a problem where I have 1 protected cell on a
sheet.
It is at the top of the sheet and it is for
a 'supervisor'
to enter his name to say that he has checked the sheet.
Only the supervisor has the password to unprotect the
sheet so he can enter data into that protected cell.
Unfortunately, sometimes the people filling out the
sheet
are wanting to insert extra rows, which I want them to
be
able to do, but they can't because the sheet is
protected.
So - I tried putting a command button on the sheet that
would unprotect the sheet, insert the desired number of
rows, then protect the sheet again
eg. {I don't mind that they can see the password in
here
at the moment}

Dim Row_Count As Integer
Dim password
password = "checked"
ActiveSheet.Unprotect password
Row_Count = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset
(Row_Count - 1, 0)).Select
Selection.EntireRow.Insert
ActiveSheet.Protect password, True, True, True

Unfortunately, when I try this, I get the following
error:
"Unprotect method of worksheet class failed"

I can guess why - password.

Can anyone suggest as alternative way of having the
sheet
unprotected so that rows can be inserted, yet that cell
still protected - even by a simple password for that
single cell?


.

.