How can I add a PASSWORD entry box to my EXCEL app using VBA

[Marcello do Guzman]

I have an application that I would like to add a password entry box.
The box would appear on the screen when a workbook is opened and the
user would have to enter a password to be able to use the workbook.
The password entry box would contain two buttons:

SUBMIT - Submits the password
CANCEL - This would close the workbook without saving.

The password would be written into the workbook (most likely in the
first spreadsheet) and the user would have to enter the password that
matches. If the password is incorrect then the workbook would display
a message box with a message like, "Sorry, but you have entered an
incorrect password. Please re-enter the correct password." The user
would be able to close this message box and try re-entering the
workbook.

I would be most grateful if you could let me know what VBA code I
would need to write to accomplish the above.

Please respond via email: (e-mail address removed)

Thank you so much.

Marcello

 

[Patrick Molloy]

Yesterday I replied that the easiest way to pasword
protect a workbook would be to save the workbook password
protected.

The alternative is quite hard.
Add a sheet with your logo or "splash" screen
set the visible property to ALL the other sheets to
xlSheetVeryHidden. This will prevent anyone unhiding them
through the menu.
In the IDE set password protection on the VBAProject.
This will prevent users re-setting th other sheets'
visible property back to xlSheetVisible.

add a userform with two text boxex, a cancel button and a
Go button. label the first textbox "User Name" and label
the second textbox "Password", setting its passwordChar
to *
call the form on the workbook opening either by writing
the form.open line in the workbook's Open event, or add a
new module and add a procedure called Auto_Open

Set the userforms ok button to test the username and
password. If it fails, set a counter to count the fails.
on the third attempt close the workbook. set the cancel
button to close the workbook


Patrick Molloy
Microsoft Excel MVP

If you want a demo workbook, please email me directly.