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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> Sorry the line frmSelectRegion.Show should be above End if.
>
> Sharad
> "Sharad Naik" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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