Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to hardcoded password ?
I have a password-protected worksheet in which users enter input into
unlocked cells. After clicking a button, VBA updates protected cells. To do this, the following statement appears in the Workbook_open proc: ThisWorkbook.Worksheets("User").Protect "mypw", True, True, True, True The worksheet is already saved with the sheet protected. I use this statement for the final "True" argument, to protect the UserInterfaceOnly but permit VBA to update the worksheet. With Excel 2002, I need to hardcode the password in the VBA. (Excel 97, 2000 did not require the password in VBA.) I would like to give the worksheet password to an administrative user to change certain locked cells before the workbook is distributed to the users. But if the administrative user inadvertantly changes the worksheet password, the VBA no longer can update the worksheet. Any alternatives to allowing VBA to update the worksheet without hardcoding the password? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to hardcoded password ?
Maybe you could write an interface that allows the administrative assistant to
update the worksheet, but only using your interface (a nice userform???). Then you won't have to share the password with anyone. But as soon as the worksheet is unprotected, I don't think there's a way to make sure that the user reprotects the worksheet using the same password. You could make it a little easier for the admin assistant to unprotect/reprotect the worksheets so that he or she won't want to use the menus to do the work. Give them a toolbar with buttons that unprotect and reprotect the worksheet. If you're lucky, he or she will use that instead. Nick wrote: I have a password-protected worksheet in which users enter input into unlocked cells. After clicking a button, VBA updates protected cells. To do this, the following statement appears in the Workbook_open proc: ThisWorkbook.Worksheets("User").Protect "mypw", True, True, True, True The worksheet is already saved with the sheet protected. I use this statement for the final "True" argument, to protect the UserInterfaceOnly but permit VBA to update the worksheet. With Excel 2002, I need to hardcode the password in the VBA. (Excel 97, 2000 did not require the password in VBA.) I would like to give the worksheet password to an administrative user to change certain locked cells before the workbook is distributed to the users. But if the administrative user inadvertantly changes the worksheet password, the VBA no longer can update the worksheet. Any alternatives to allowing VBA to update the worksheet without hardcoding the password? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to hardcoded password ?
Thanks, I was afraid I was out of luck.
I've tried the userform route. I wound up displaying the admin worksheet only if a userform password (not the worksheet passsword) was OK. VBA then let user edit the admin worksheet. But that's another hardcoded password. I was hoping to let the admin choose the password. Nick "Dave Peterson" wrote: Maybe you could write an interface that allows the administrative assistant to update the worksheet, but only using your interface (a nice userform???). Then you won't have to share the password with anyone. But as soon as the worksheet is unprotected, I don't think there's a way to make sure that the user reprotects the worksheet using the same password. You could make it a little easier for the admin assistant to unprotect/reprotect the worksheets so that he or she won't want to use the menus to do the work. Give them a toolbar with buttons that unprotect and reprotect the worksheet. If you're lucky, he or she will use that instead. Nick wrote: I have a password-protected worksheet in which users enter input into unlocked cells. After clicking a button, VBA updates protected cells. To do this, the following statement appears in the Workbook_open proc: ThisWorkbook.Worksheets("User").Protect "mypw", True, True, True, True The worksheet is already saved with the sheet protected. I use this statement for the final "True" argument, to protect the UserInterfaceOnly but permit VBA to update the worksheet. With Excel 2002, I need to hardcode the password in the VBA. (Excel 97, 2000 did not require the password in VBA.) I would like to give the worksheet password to an administrative user to change certain locked cells before the workbook is distributed to the users. But if the administrative user inadvertantly changes the worksheet password, the VBA no longer can update the worksheet. Any alternatives to allowing VBA to update the worksheet without hardcoding the password? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to hardcoded password ?
Instead of having the code protect/unprotect the worksheet, you could throw up
that protection dialog. Application.Dialogs(xlDialogProtectDocument).Show Just check to see if the worksheet is protected first: Option Explicit Sub testme01() If WksIsProtected(Worksheets("sheet1")) = True Then 'show the unprotect worksheet dialog Application.Dialogs(xlDialogProtectDocument).Show End If 'do a bunch of work Do If WksIsProtected(Worksheets("sheet1")) Then Exit Do 'show the protection dialog Application.Dialogs(xlDialogProtectDocument).Show Loop End Sub Function WksIsProtected(wks As Worksheet) As Boolean If wks.ProtectContents = True _ Or wks.ProtectDrawingObjects = True _ Or wks.ProtectScenarios = True Then WksIsProtected = True Else WksIsProtected = False End If End Function Nick wrote: Thanks, I was afraid I was out of luck. I've tried the userform route. I wound up displaying the admin worksheet only if a userform password (not the worksheet passsword) was OK. VBA then let user edit the admin worksheet. But that's another hardcoded password. I was hoping to let the admin choose the password. Nick "Dave Peterson" wrote: Maybe you could write an interface that allows the administrative assistant to update the worksheet, but only using your interface (a nice userform???). Then you won't have to share the password with anyone. But as soon as the worksheet is unprotected, I don't think there's a way to make sure that the user reprotects the worksheet using the same password. You could make it a little easier for the admin assistant to unprotect/reprotect the worksheets so that he or she won't want to use the menus to do the work. Give them a toolbar with buttons that unprotect and reprotect the worksheet. If you're lucky, he or she will use that instead. Nick wrote: I have a password-protected worksheet in which users enter input into unlocked cells. After clicking a button, VBA updates protected cells. To do this, the following statement appears in the Workbook_open proc: ThisWorkbook.Worksheets("User").Protect "mypw", True, True, True, True The worksheet is already saved with the sheet protected. I use this statement for the final "True" argument, to protect the UserInterfaceOnly but permit VBA to update the worksheet. With Excel 2002, I need to hardcode the password in the VBA. (Excel 97, 2000 did not require the password in VBA.) I would like to give the worksheet password to an administrative user to change certain locked cells before the workbook is distributed to the users. But if the administrative user inadvertantly changes the worksheet password, the VBA no longer can update the worksheet. Any alternatives to allowing VBA to update the worksheet without hardcoding the password? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to hardcoded password ?
In addition to Dave's suggestion about providing an interface, assuming VBA
protection is not the issue maybe in the before save event you could attempt to test protection. If it fails with your password cancel the save with appropriate message to your user. Regards, Peter T "Nick" wrote in message ... I have a password-protected worksheet in which users enter input into unlocked cells. After clicking a button, VBA updates protected cells. To do this, the following statement appears in the Workbook_open proc: ThisWorkbook.Worksheets("User").Protect "mypw", True, True, True, True The worksheet is already saved with the sheet protected. I use this statement for the final "True" argument, to protect the UserInterfaceOnly but permit VBA to update the worksheet. With Excel 2002, I need to hardcode the password in the VBA. (Excel 97, 2000 did not require the password in VBA.) I would like to give the worksheet password to an administrative user to change certain locked cells before the workbook is distributed to the users. But if the administrative user inadvertantly changes the worksheet password, the VBA no longer can update the worksheet. Any alternatives to allowing VBA to update the worksheet without hardcoding the password? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to hardcoded password ?
Thanks. Dave's suggestion to use the dialog in conjunction with checking the
password in the BeforeSave event should allow this to work. I tested it here, time will tell how well it works in the field. Appreciate the suggestions. "Peter T" wrote: In addition to Dave's suggestion about providing an interface, assuming VBA protection is not the issue maybe in the before save event you could attempt to test protection. If it fails with your password cancel the save with appropriate message to your user. Regards, Peter T "Nick" wrote in message ... I have a password-protected worksheet in which users enter input into unlocked cells. After clicking a button, VBA updates protected cells. To do this, the following statement appears in the Workbook_open proc: ThisWorkbook.Worksheets("User").Protect "mypw", True, True, True, True The worksheet is already saved with the sheet protected. I use this statement for the final "True" argument, to protect the UserInterfaceOnly but permit VBA to update the worksheet. With Excel 2002, I need to hardcode the password in the VBA. (Excel 97, 2000 did not require the password in VBA.) I would like to give the worksheet password to an administrative user to change certain locked cells before the workbook is distributed to the users. But if the administrative user inadvertantly changes the worksheet password, the VBA no longer can update the worksheet. Any alternatives to allowing VBA to update the worksheet without hardcoding the password? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use Find without hardcoded value but a value from a cell | Excel Worksheet Functions | |||
I need to change hardcoded background color | Excel Worksheet Functions | |||
Change hardcoded filename | Excel Programming | |||
VBA Code To find Hardcoded Values in Formula | Excel Programming | |||
DSUM with Criteria containing non-hardcoded values? | Excel Worksheet Functions |