Alternative to hardcoded password ?

[Guest]

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]

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.

 

[Guest]

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]

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

 

[Peter T]

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

 

[Guest]

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.