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

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"
crosoft.com wrote in
message ...
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

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

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

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"
crosoft.com wrote in
message ...
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

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

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"
crosoft.com wrote in
message ...
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