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


---

Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.797 / Virus Database: 541 - Release Date: 11/15/2004

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


"L Mehl" wrote in message
...
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


---

Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.797 / Virus Database: 541 - Release Date: 11/15/2004

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

Sharad
"Sharad Naik" wrote in message
...
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


"L Mehl" wrote in message
...
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


---

Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.797 / Virus Database: 541 - Release Date: 11/15/2004

Sharad --

That is what I needed.

Thanks for the solution.

Larry

"Sharad Naik" wrote in message
...
Sorry the line frmSelectRegion.Show should be above End if.

Sharad
"Sharad Naik" wrote in message
...
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


"L Mehl" wrote in message
...
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


---

Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.797 / Virus Database: 541 - Release Date: 11/15/2004








---

Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.797 / Virus Database: 541 - Release Date: 11/16/2004