Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting/Unprotecting all sheets with macro leads to "image" iss | Excel Worksheet Functions | |||
Unprotecting Sheets | New Users to Excel | |||
Unprotecting password protected workbook | Excel Discussion (Misc queries) | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) | |||
Beta - unprotecting multiple sheets | Excel Programming |