Userform, worksheet protection

[dantb]

Hi All, Is there anyway of having a form on open, that if the user does not enter the correct code say (data), that I could have a message (Access denied) and the workbook would automatically close? Thx Dan.

[RWinfield]

You need a userform (obviously) with a text box and one command button. Modify the names as needed.

Add the following code to the workbook open event. Right click on the Excel icon on the extreme left side of the toolbar and choose "View Code"

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Add this code to the userform module.

Private Sub CommandButton1_Click()
If TextBox1 <> "data" Then
Application.DisplayAlerts = False
MsgBox "Invalid Password - Excel will now close", vbExclamation, "Invalid Password"
Application.Quit
Application.DisplayAlerts = True
Else
Unload Me ' Unloads the userform and allows acess to the workbook.
End If
End Sub

The following code disables the "X" on the userform to prevent closing it that way. This would be placed in the userform module as well.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End Sub

There are many other ways to add layers of protection to a workbook, but they are all basically like a padlock...they will only keep honest people honest. If someone wants into the file bad enough there is almost always a way around any protection.

Hope this helps,

Rick


[ This Message was edited by: RWinfield on 2002-04-20 23:05 ]

[ This Message was edited by: RWinfield on 2002-04-20 23:06 ]

[dantb]

Thanks RWinfield, Works great. Im sure, like you said, anyone that wants in bad enough will get in. But Atleast this will help keeping the average user honest. Thanks again & have a good day. Dan