Userform, worksheet protection

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top