Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Hi,
I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Can you post the code you are using and mark the line that fails
-- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
These are the two subs. I took out the password prompt for the 'ProtectAll'
sub to simplify it while I get the unprotect working! Sub ProtectAll() Dim WS As Worksheet 'Dim Pword As String 'Pword = Application.InputBox("Enter password to lock spreadsheet", "Password Check") For Each WS In ActiveWorkbook.Worksheets WS.Protect Password:="Fred" Next WS ThisWorkbook.Protect Password:="Fred", Structu=True End Sub Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Oops, forgot to mark the failing line:
In this version it goes from input box to 'endit' so there's blatantly an error. If I take 'On Error' out it comes up with: 'Run-time error 1004. The password you supplied is not correct...' Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Untested but try unprotecting in the reverse order of protecting. That is
unprotect the workbook first then the worksheets. If this does not work, on which line does the code fail? -- Regards, OssieMac "Bug289" wrote: These are the two subs. I took out the password prompt for the 'ProtectAll' sub to simplify it while I get the unprotect working! Sub ProtectAll() Dim WS As Worksheet 'Dim Pword As String 'Pword = Application.InputBox("Enter password to lock spreadsheet", "Password Check") For Each WS In ActiveWorkbook.Worksheets WS.Protect Password:="Fred" Next WS ThisWorkbook.Protect Password:="Fred", Structu=True End Sub Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
see if this does what you want:
Sub UnprotectSheets() Dim SH As Single, pwd top: On Error GoTo myerror pwd = InputBox("Enter password") If Len(pwd) = 0 Then GoTo ExitSub Application.ScreenUpdating = False For SH = 1 To Sheets.Count Sheets(SH).Unprotect Password:=pwd Next SH ThisWorkbook.Unprotect Password:=pwd myerror: If Err 0 Then MsgBox (Error(Err)) Err.Clear GoTo top End If ExitSub: Application.ScreenUpdating = True End Sub -- jb "Bug289" wrote: Oops, forgot to mark the failing line: In this version it goes from input box to 'endit' so there's blatantly an error. If I take 'On Error' out it comes up with: 'Run-time error 1004. The password you supplied is not correct...' Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Nope.
It fails on wks.Unprotect Password:=Pword It does unprotect the workbook if I put that first "OssieMac" wrote: Untested but try unprotecting in the reverse order of protecting. That is unprotect the workbook first then the worksheets. If this does not work, on which line does the code fail? -- Regards, OssieMac "Bug289" wrote: These are the two subs. I took out the password prompt for the 'ProtectAll' sub to simplify it while I get the unprotect working! Sub ProtectAll() Dim WS As Worksheet 'Dim Pword As String 'Pword = Application.InputBox("Enter password to lock spreadsheet", "Password Check") For Each WS In ActiveWorkbook.Worksheets WS.Protect Password:="Fred" Next WS ThisWorkbook.Protect Password:="Fred", Structu=True End Sub Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Sorry, no.
Now it just brings up the error in a message box before returning to the top, I type the password again and I get the same runtime error. Same line is failing. There's something wrong with the '.Unprotect Password:=pwd' line. "john" wrote: see if this does what you want: Sub UnprotectSheets() Dim SH As Single, pwd top: On Error GoTo myerror pwd = InputBox("Enter password") If Len(pwd) = 0 Then GoTo ExitSub Application.ScreenUpdating = False For SH = 1 To Sheets.Count Sheets(SH).Unprotect Password:=pwd Next SH ThisWorkbook.Unprotect Password:=pwd myerror: If Err 0 Then MsgBox (Error(Err)) Err.Clear GoTo top End If ExitSub: Application.ScreenUpdating = True End Sub -- jb "Bug289" wrote: Oops, forgot to mark the failing line: In this version it goes from input box to 'endit' so there's blatantly an error. If I take 'On Error' out it comes up with: 'Run-time error 1004. The password you supplied is not correct...' Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
worked ok for me
error suggests that all your sheets may not be using same password? Try unprotecting manually & then reapply your password to all sheets to ensure all same. -- jb "Bug289" wrote: Sorry, no. Now it just brings up the error in a message box before returning to the top, I type the password again and I get the same runtime error. Same line is failing. There's something wrong with the '.Unprotect Password:=pwd' line. "john" wrote: see if this does what you want: Sub UnprotectSheets() Dim SH As Single, pwd top: On Error GoTo myerror pwd = InputBox("Enter password") If Len(pwd) = 0 Then GoTo ExitSub Application.ScreenUpdating = False For SH = 1 To Sheets.Count Sheets(SH).Unprotect Password:=pwd Next SH ThisWorkbook.Unprotect Password:=pwd myerror: If Err 0 Then MsgBox (Error(Err)) Err.Clear GoTo top End If ExitSub: Application.ScreenUpdating = True End Sub -- jb "Bug289" wrote: Oops, forgot to mark the failing line: In this version it goes from input box to 'endit' so there's blatantly an error. If I take 'On Error' out it comes up with: 'Run-time error 1004. The password you supplied is not correct...' Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet unprotection not working
Doh! It was a hidden spreadsheet that still had some other password on it!
Thanks, you're a star! "john" wrote: worked ok for me error suggests that all your sheets may not be using same password? Try unprotecting manually & then reapply your password to all sheets to ensure all same. -- jb "Bug289" wrote: Sorry, no. Now it just brings up the error in a message box before returning to the top, I type the password again and I get the same runtime error. Same line is failing. There's something wrong with the '.Unprotect Password:=pwd' line. "john" wrote: see if this does what you want: Sub UnprotectSheets() Dim SH As Single, pwd top: On Error GoTo myerror pwd = InputBox("Enter password") If Len(pwd) = 0 Then GoTo ExitSub Application.ScreenUpdating = False For SH = 1 To Sheets.Count Sheets(SH).Unprotect Password:=pwd Next SH ThisWorkbook.Unprotect Password:=pwd myerror: If Err 0 Then MsgBox (Error(Err)) Err.Clear GoTo top End If ExitSub: Application.ScreenUpdating = True End Sub -- jb "Bug289" wrote: Oops, forgot to mark the failing line: In this version it goes from input box to 'endit' so there's blatantly an error. If I take 'On Error' out it comes up with: 'Run-time error 1004. The password you supplied is not correct...' Sub UnprotectAll() Dim wks As Worksheet Pword = InputBox("enter the password") On Error GoTo endit For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Password:=Pword Next wks ThisWorkbook.Unprotect Password:=Pword endit: MsgBox "Incorrect Password" End Sub "OssieMac" wrote: Can you post the code you are using and mark the line that fails -- Regards, OssieMac "Bug289" wrote: Hi, I have trawled through the substantial number of posts in the discussion group on protecting and unprotecting multiple spreadsheets at the same time...and been semi-successful. I am protecting a spreadsheet so that my boss can edit the financially sensitive areas of the sheets and then protect it so that they can't be edited/seen by others entering data on the same sheets. I want to make it as easy as possible for him to protect and unprotect without having to find the VB editor so I have set up command buttons to run a protect and unprotect sub. However, I need them to prompt him for a password. I can get protect to work without any issues but when I try to get it to unprotect the code fails, flips to the VB editor and tells me the password is incorrect even if it isn't. The versions of code in the discussion group that involve an 'On Error Goto..' line, 'Goto' everytime! I'm sure I'm missing something obvious but can someone tell me what! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking / Unlocking Cell with out Protection/ Unprotection of Shee | Excel Programming | |||
Sheet protection/unprotection | Excel Programming | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
Unprotection of outlined data in protected worksheet | Excel Discussion (Misc queries) | |||
Unprotection once performed... | Excel Discussion (Misc queries) |