Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password not working on exported Workbook / Worksheet.
Hi;
I have built a little spreadsheet project form a club I am a member of. I have one sheet of the Workbook password protected in order to make some hidden columns in accessable to anyone other than executive members. The password protection works fine on my computer, but when I transfer the file to other club members the are not asked for a pass word wen they open the hidden columns. Below is the VBA code I use to lock and protect those columns. Sub ExecProtect() ' ' ExecProtect Macro ' Macro recorded 08/21/2006 by William Case ' ' Worksheets("Member_List").Protect _ Password:="bill", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ UserInterFaceOnly:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=False, _ AllowFormattingRows:=True, _ AllowInsertingColumns:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowDeletingColumns:=True, _ AllowDeletingRows:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub Sub LockDef() Sub BarLock() ' ' BarLock Macro ' Macro recorded 08/21/2006 by William Case ' ' Test and lock bar, address and fees columns ' ' Worksheets("Member_List").Range("DataArea").Locked = False Range(BarsToLock).Locked = True Range(AddressToLock).Locked = True Range(FeesToLock).Locked = True End Sub BarsToLock = "Bar1D,Bar2J,Bar3O,Bar4T,Bar5Y,Bar6AD,Bar7AI,Bar8A R,Bar9AZ" AddressToLock = "Addresses" FeesToLock = "Fees" End Sub Sub OpenAddresses() ' ' ExecProtect Macro ' Macro recorded 08/24/2006 by William Case ' On Error GoTo OpenAddresses_Error ExecUnprotect Range(AddressToLock).Locked = False UnHideAddresses ExecProtect ' On Error GoTo 0 Exit Sub OpenAddresses_Error: If Err.Description = "Method 'Range' of object '_Global' failed" Then LockDef Resume Next Else MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenAddresses of Module LockandProtectMod" End If End Sub There is several other procedures to cover a complex range of possibilites. They all work for me as they should. But the password 'bill' is not asked for when this project is installed on another Excel using machine and another user unhides hidden columns. What have I missed? Regards Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password not working on exported Workbook / Worksheet.
Would they not have to run the macro again to reset the password when it is
set by code? I am not sure how that works. "Bill Case" wrote: Hi; I have built a little spreadsheet project form a club I am a member of. I have one sheet of the Workbook password protected in order to make some hidden columns in accessable to anyone other than executive members. The password protection works fine on my computer, but when I transfer the file to other club members the are not asked for a pass word wen they open the hidden columns. Below is the VBA code I use to lock and protect those columns. Sub ExecProtect() ' ' ExecProtect Macro ' Macro recorded 08/21/2006 by William Case ' ' Worksheets("Member_List").Protect _ Password:="bill", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ UserInterFaceOnly:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=False, _ AllowFormattingRows:=True, _ AllowInsertingColumns:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowDeletingColumns:=True, _ AllowDeletingRows:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub Sub LockDef() Sub BarLock() ' ' BarLock Macro ' Macro recorded 08/21/2006 by William Case ' ' Test and lock bar, address and fees columns ' ' Worksheets("Member_List").Range("DataArea").Locked = False Range(BarsToLock).Locked = True Range(AddressToLock).Locked = True Range(FeesToLock).Locked = True End Sub BarsToLock = "Bar1D,Bar2J,Bar3O,Bar4T,Bar5Y,Bar6AD,Bar7AI,Bar8A R,Bar9AZ" AddressToLock = "Addresses" FeesToLock = "Fees" End Sub Sub OpenAddresses() ' ' ExecProtect Macro ' Macro recorded 08/24/2006 by William Case ' On Error GoTo OpenAddresses_Error ExecUnprotect Range(AddressToLock).Locked = False UnHideAddresses ExecProtect ' On Error GoTo 0 Exit Sub OpenAddresses_Error: If Err.Description = "Method 'Range' of object '_Global' failed" Then LockDef Resume Next Else MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenAddresses of Module LockandProtectMod" End If End Sub There is several other procedures to cover a complex range of possibilites. They all work for me as they should. But the password 'bill' is not asked for when this project is installed on another Excel using machine and another user unhides hidden columns. What have I missed? Regards Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password not working on exported Workbook / Worksheet.
Hi JLGWhiz;
"JLGWhiz" wrote: Would they not have to run the macro again to reset the password when it is set by code? I am not sure how that works. I am not sure how the whole password thing works in Excel. What I have read isn't more enlightening. I use OfficeXP (i.e Excel 2003) my friend uses Excel 2000 as a stand alone. Every thing on my machine works as it is supposed to. Yes, password was set by the Worksheet.Protect properies. So, if it works for me, the same VBA code should make it work for him. However, when he opens the workbook I sent to him or uses the Unhide macro I wrote, he gets an error message generated by On Error, and no password entry dialogue. The hidden columns Unhide anyways. "Bill Case" wrote: What have I missed? Regards Bill |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password not working on exported Workbook / Worksheet.
First, officeXP is Excel 2002, not Excel 2003. Office 2003 contains Excel
2003 xl2000 has a different protection model than Excel 2002 (office XP) and Excel 2003. Not sure how protection is managed in your workbook, but it is possible your problem is related to this. -- Regards, Tom Ogilvy "Bill Case" wrote in message ... Hi JLGWhiz; "JLGWhiz" wrote: Would they not have to run the macro again to reset the password when it is set by code? I am not sure how that works. I am not sure how the whole password thing works in Excel. What I have read isn't more enlightening. I use OfficeXP (i.e Excel 2003) my friend uses Excel 2000 as a stand alone. Every thing on my machine works as it is supposed to. Yes, password was set by the Worksheet.Protect properies. So, if it works for me, the same VBA code should make it work for him. However, when he opens the workbook I sent to him or uses the Unhide macro I wrote, he gets an error message generated by On Error, and no password entry dialogue. The hidden columns Unhide anyways. "Bill Case" wrote: What have I missed? Regards Bill |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password not working on exported Workbook / Worksheet.
Hi Tom;
Exactly. At the risk of appearing dim-witted, I want to know how to write the protection for Excel 2000 (and other versions if necessary) I should be able to get the users version number through 'Application.Version' and if it's not 10.* (Excel 2002 --mine), I should be able to write alternative protection code. I have googled everywhere found lots of commentary and statements about protection improvement in 2002 but no examples of how to deal with an earlier version of Excel. Excel 2000 is probably all I need. Regards Bill "Tom Ogilvy" wrote: First, officeXP is Excel 2002, not Excel 2003. Office 2003 contains Excel 2003 xl2000 has a different protection model than Excel 2002 (office XP) and Excel 2003. Not sure how protection is managed in your workbook, but it is possible your problem is related to this. -- Regards, Tom Ogilvy "Bill Case" wrote in message ... Hi JLGWhiz; "JLGWhiz" wrote: Would they not have to run the macro again to reset the password when it is set by code? I am not sure how that works. I am not sure how the whole password thing works in Excel. What I have read isn't more enlightening. I use OfficeXP (i.e Excel 2003) my friend uses Excel 2000 as a stand alone. Every thing on my machine works as it is supposed to. Yes, password was set by the Worksheet.Protect properies. So, if it works for me, the same VBA code should make it work for him. However, when he opens the workbook I sent to him or uses the Unhide macro I wrote, he gets an error message generated by On Error, and no password entry dialogue. The hidden columns Unhide anyways. "Bill Case" wrote: What have I missed? Regards Bill |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password not working on exported Workbook / Worksheet.
Here is the command in Excel 2000
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True If you need the userInterfaceOnly agument, it has to be issued each time the workbook is opened ActiveSheet.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True, UserInterfaceOnly:=True -- Regards, Tom Ogilvy "Bill Case" wrote in message ... Hi Tom; Exactly. At the risk of appearing dim-witted, I want to know how to write the protection for Excel 2000 (and other versions if necessary) I should be able to get the users version number through 'Application.Version' and if it's not 10.* (Excel 2002 --mine), I should be able to write alternative protection code. I have googled everywhere found lots of commentary and statements about protection improvement in 2002 but no examples of how to deal with an earlier version of Excel. Excel 2000 is probably all I need. Regards Bill "Tom Ogilvy" wrote: First, officeXP is Excel 2002, not Excel 2003. Office 2003 contains Excel 2003 xl2000 has a different protection model than Excel 2002 (office XP) and Excel 2003. Not sure how protection is managed in your workbook, but it is possible your problem is related to this. -- Regards, Tom Ogilvy "Bill Case" wrote in message ... Hi JLGWhiz; "JLGWhiz" wrote: Would they not have to run the macro again to reset the password when it is set by code? I am not sure how that works. I am not sure how the whole password thing works in Excel. What I have read isn't more enlightening. I use OfficeXP (i.e Excel 2003) my friend uses Excel 2000 as a stand alone. Every thing on my machine works as it is supposed to. Yes, password was set by the Worksheet.Protect properies. So, if it works for me, the same VBA code should make it work for him. However, when he opens the workbook I sent to him or uses the Unhide macro I wrote, he gets an error message generated by On Error, and no password entry dialogue. The hidden columns Unhide anyways. "Bill Case" wrote: What have I missed? Regards Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet, workbook password | Excel Worksheet Functions | |||
Excel workbook password - not working | Excel Discussion (Misc queries) | |||
How do I password protect a single worksheet within a workbook f | Excel Discussion (Misc queries) | |||
my password quit working in an excel workbook, help! | Excel Worksheet Functions | |||
Why am I prompted for the workbook password for each worksheet? | Excel Worksheet Functions |