Need a psswd verification form to open when the file is opened

[Linking to specific cells in pivot table]

Hi - I'd like to do the following and am hoping to get guidance on how to do
this:

- Create a form which asks the user to input a password -- if the password
is correct, the user is able to access the spreadsheet. If the password is
incorrect, the user is not able to access the spreadsheet.

- Have the form described above automatically open when the file is opened.

I have a created a form in VB, but am honestly not sure how I should
approach the password issue and then how to get it to automatically pop up
when the file is opened (I'm assuming I can initiate the form under 'Option
Explicit', but haven't been able to figure out the sytax for initiating a
form there).

Any help you can provide is appreciated!

Robert

[William Benson]

Fro everything I know this is impossible. You would put such a function in
the Workbook_Open event, but the user could just break the routine with
Ctrl-Break, or open it without macros and look around for the required
password.

Why not just save the file password protected?
File-Save As
Click Tools drop down and select "General Options", set a password ... prior
to clicking Save

Bill
"Linking to specific cells in pivot table"
<[email protected]> wrote in
message news:[email protected]...
> Hi - I'd like to do the following and am hoping to get guidance on how to
> do
> this:
>
> - Create a form which asks the user to input a password -- if the password
> is correct, the user is able to access the spreadsheet. If the password
> is
> incorrect, the user is not able to access the spreadsheet.
>
> - Have the form described above automatically open when the file is
> opened.
>
> I have a created a form in VB, but am honestly not sure how I should
> approach the password issue and then how to get it to automatically pop up
> when the file is opened (I'm assuming I can initiate the form under
> 'Option
> Explicit', but haven't been able to figure out the sytax for initiating a
> form there).
>
> Any help you can provide is appreciated!
>
> Robert

[Linking to specific cells in pivot table]

Thanks Bill!! Now that I set up the password in this way, is there a way
that I can have custom text displayed when the user tries to enter an invalid
password or hits the 'Cancel' button? Basically, I want to put a custom
message that tells the user who to contact in order to get the password...

Thanks!

Robert

"William Benson" wrote:

> Fro everything I know this is impossible. You would put such a function in
> the Workbook_Open event, but the user could just break the routine with
> Ctrl-Break, or open it without macros and look around for the required
> password.
>
> Why not just save the file password protected?
> File-Save As
> Click Tools drop down and select "General Options", set a password ... prior
> to clicking Save
>
> Bill
> "Linking to specific cells in pivot table"
> <[email protected]> wrote in
> message news:[email protected]...
> > Hi - I'd like to do the following and am hoping to get guidance on how to
> > do
> > this:
> >
> > - Create a form which asks the user to input a password -- if the password
> > is correct, the user is able to access the spreadsheet. If the password
> > is
> > incorrect, the user is not able to access the spreadsheet.
> >
> > - Have the form described above automatically open when the file is
> > opened.
> >
> > I have a created a form in VB, but am honestly not sure how I should
> > approach the password issue and then how to get it to automatically pop up
> > when the file is opened (I'm assuming I can initiate the form under
> > 'Option
> > Explicit', but haven't been able to figure out the sytax for initiating a
> > form there).
> >
> > Any help you can provide is appreciated!
> >
> > Robert
>
>
>

[Madiya]

I think it is possible. If not fully secure, security can be enhanced
to some extent. Or for that reason, security is always too week in
excel and any password can be cracked. Many code snippets and softwares
are available on net for free of cost. So the best thing is not to rely
on the excel security. Having said that, here is the solution.


While absolute protection may not be available, try putting a dummy
sheet with instructions on how to enable macros to access the file.
Hide all the sheets except dummy (xlveryhidden) in "before close"
event.
In workbook open event, check and verify the password. Depending on
password, make all the relavent sheets visible and hide
(xlveryhidden) the dummy sheet. This way if macro is disabled, your
data sheets will remain hidden and if macro is enabled, data sheets
will be displayed. Be sure to password protect your VBA Project also
along with checking the "Lock project for viewing" so that even after
getting access to sheets with proper passwords, one can not see the
others passwords.

Hope this is of some help.

Regards,
Madiya

[Linking to specific cells in pivot table]

Thanks Madiya -- I think protecting the workbook will do. Thanks for the
suggestion on protecting my VBA Project - I've done this now.

The question I still have is whether or not I can have a custom message or
if I can cusotmize the message that comes up when someone enters an invalid
password or selects the 'Cancel' button when prompted for the password.

Thanks!

Robert

"Madiya" wrote:

> I think it is possible. If not fully secure, security can be enhanced
> to some extent. Or for that reason, security is always too week in
> excel and any password can be cracked. Many code snippets and softwares
> are available on net for free of cost. So the best thing is not to rely
> on the excel security. Having said that, here is the solution.
>
>
> While absolute protection may not be available, try putting a dummy
> sheet with instructions on how to enable macros to access the file.
> Hide all the sheets except dummy (xlveryhidden) in "before close"
> event.
> In workbook open event, check and verify the password. Depending on
> password, make all the relavent sheets visible and hide
> (xlveryhidden) the dummy sheet. This way if macro is disabled, your
> data sheets will remain hidden and if macro is enabled, data sheets
> will be displayed. Be sure to password protect your VBA Project also
> along with checking the "Lock project for viewing" so that even after
> getting access to sheets with proper passwords, one can not see the
> others passwords.
>
> Hope this is of some help.
>
> Regards,
> Madiya
>
>

[William Benson]

Madiya's reply is correct. I was mistaken, by protecting the Project from
viewing, you can cause them not to be able to see the acceptable password. I
would use this method for sure -- but woe unto you if you forget the
password... you might have to spend $5 or $10 on the internet getting a
password cracker!!!

I also think the method to hide every sheet is good, but another way is to
only show cell IV65536.


Private Sub Workbook_Open()
On Error Resume Next
ActiveSheet.Range("IV65536").Select
Application.ScreenUpdating = False
If InputBox("Enter Pasword", "Enter correct password or the file will
close") <> "SecretCode" Then
MsgBox "Closing - authorization failed"
ThisWorkbook.Close
Else
MsgBox "Authorization granted ... please proceed"
Application.ScreenUpdating = True
ActiveSheet.Range("A1").Select
End If
End Sub




"Linking to specific cells in pivot table"
<[email protected]> wrote in
message news:[email protected]...
> Thanks Madiya -- I think protecting the workbook will do. Thanks for the
> suggestion on protecting my VBA Project - I've done this now.
>
> The question I still have is whether or not I can have a custom message or
> if I can cusotmize the message that comes up when someone enters an
> invalid
> password or selects the 'Cancel' button when prompted for the password.
>
> Thanks!
>
> Robert
>
> "Madiya" wrote:
>
>> I think it is possible. If not fully secure, security can be enhanced
>> to some extent. Or for that reason, security is always too week in
>> excel and any password can be cracked. Many code snippets and softwares
>> are available on net for free of cost. So the best thing is not to rely
>> on the excel security. Having said that, here is the solution.
>>
>>
>> While absolute protection may not be available, try putting a dummy
>> sheet with instructions on how to enable macros to access the file.
>> Hide all the sheets except dummy (xlveryhidden) in "before close"
>> event.
>> In workbook open event, check and verify the password. Depending on
>> password, make all the relavent sheets visible and hide
>> (xlveryhidden) the dummy sheet. This way if macro is disabled, your
>> data sheets will remain hidden and if macro is enabled, data sheets
>> will be displayed. Be sure to password protect your VBA Project also
>> along with checking the "Lock project for viewing" so that even after
>> getting access to sheets with proper passwords, one can not see the
>> others passwords.
>>
>> Hope this is of some help.
>>
>> Regards,
>> Madiya
>>
>>

[Dave Peterson]

I don't think you'll be able to have a custom message that's buried in the code
for that workbook.

If the user can't open the file, then the macros won't run.

You could have another helper workbook that tries to open the protected workbook
and if it fails, then you could give it any message you want.





Linking to specific cells in pivot table wrote:
>
> Thanks Madiya -- I think protecting the workbook will do. Thanks for the
> suggestion on protecting my VBA Project - I've done this now.
>
> The question I still have is whether or not I can have a custom message or
> if I can cusotmize the message that comes up when someone enters an invalid
> password or selects the 'Cancel' button when prompted for the password.
>
> Thanks!
>
> Robert
>
> "Madiya" wrote:
>
> > I think it is possible. If not fully secure, security can be enhanced
> > to some extent. Or for that reason, security is always too week in
> > excel and any password can be cracked. Many code snippets and softwares
> > are available on net for free of cost. So the best thing is not to rely
> > on the excel security. Having said that, here is the solution.
> >
> >
> > While absolute protection may not be available, try putting a dummy
> > sheet with instructions on how to enable macros to access the file.
> > Hide all the sheets except dummy (xlveryhidden) in "before close"
> > event.
> > In workbook open event, check and verify the password. Depending on
> > password, make all the relavent sheets visible and hide
> > (xlveryhidden) the dummy sheet. This way if macro is disabled, your
> > data sheets will remain hidden and if macro is enabled, data sheets
> > will be displayed. Be sure to password protect your VBA Project also
> > along with checking the "Lock project for viewing" so that even after
> > getting access to sheets with proper passwords, one can not see the
> > others passwords.
> >
> > Hope this is of some help.
> >
> > Regards,
> > Madiya
> >
> >

--

Dave Peterson