Comparing password entry vs stored password - 2003

[L Mehl]

Hello --

A worksheet has a button which, when clicked, shows a list of sales regions
and allows changing the region on a quotation.
Users of the sheet are permitted to change the region, prior to preparing
the quote.

The completed quote (the workbook containing the sheet) is sent to the
customer, who can change other cells on the sheet (sensitivity analysis),
but not the sales region.

I currently have the following code on the button click to control access to
the regions list:
....
frmPWEntry.Show 'contains 1 field: txtPWEnter, for entry of password,
masked with "****"

strPassword = frmPWEntry.txtPWEnter.Value

If strPassword = "1234" Then
frmSelectRegion.Show 'listbox allows selection of region
End If

Unload frmPWEntry
....

Problem: User will occasionally want to change the workbook password via
menu selections, and would prefer not to also have to change VBA behind the
workbook in order to maintain the button's "protection".

Tom Ogilvy's recent mention of "ActiveWorkbook.HasPassword = ..." prompts me
to ask the following:

Is there an ActiveWorkbook. ... which knows the password and against which
VBA can test ("behind the scenes") the entered password string?

If so, my code could look like

strPassword = frmPWEntry.txtPWEnter.Value

If ActiveWorkbook. ... = strPassword Then
frmSelectRegion.Show
End If


Thanks for any help.

Larry Mehl

 

[Sharad Naik]

There is ActiveWorkBook.Password Property to read the password but
it returns the encrypted password and you can't compare it with your string.

What you can do it as under:

Err.Clear
On Error Resume Next
ActiveWorkbook.Unprotect (strPassword)
If Err = 0 Then
ActiveWorkbook.Protect strPassword, True, True
On Error GoTo 0
End If
frmSelectRegion.Show

I.e. you try to unportect the workbook, with the password provided by the
user.
If the password is not correct, an error is generated (Err <> 0.). If no
error is generated (Err=0),
it means the password was correct and workbook has been unprotected.
So you execute your code, protect back the workbook with same password.

Sharad

 

[Sharad Naik]

Sorry the line frmSelectRegion.Show should be above End if.

Sharad

 

[L Mehl]

Sharad --

That is what I needed.

Thanks for the solution.

Larry