Password Protecting

N

Nick

I am trying to write some code so when a button is pressed a password message
comes up and needs to be entered for accessing the information.

Any ideas people. Would be very grateful as been trying this for a while now
 
P

Paul B

Nick, something like this, you will need lock the VBA project so you can't
see the password in it


Sub PassWord_To_Run()
'must lock VBA project so you can't see the password in it
Dim MyStr1 As String, MyStr2 As String
MyStr2 = ("123") 'This is the password and it is CASE sensitive
MyStr1 = InputBox("Password Required")
If MyStr1 = MyStr2 Then

'your code here

Else
MsgBox ("Access Denied")
End If
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

Nick said:
I am trying to write some code so when a button is pressed a password message
comes up and needs to be entered for accessing the information.

Any ideas people. Would be very grateful as been trying this for a while
Click to expand...
now
 
N

Nick

Cheers mate, thats fantastic. Just one other question. Can you do the same so
if the sheet2 is clicked (at bottom left of excel sheet) a password box
appears. I want to set my program up so access to sheet2 is password
protected so no-one can view its contents.
 
P

Paul B

Nick, here is some code, to put in this macro right click on the worksheet
tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook.

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="123"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to view this sheet")

If strPassword = "" Then
Me.Previous.Select
Exit Sub
ElseIf strPassword <> "123" Then
MsgBox "Password Incorrect "
Me.Previous.Select
Exit Sub
Else
Me.Unprotect Password:="123"
Me.Columns.Hidden = False
End If

On Error GoTo 0
End Sub


Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub


Be aware that sheet passwords are very easy to break and all somebody would
have to do is open the workbook with macros disabled unprotect the sheet and
then unhide the columns to see your data, this will keep out some people but
if somebody wants to see your data in Excel they will find a way, Excel is
not a secure platform.
You will also need to protect the VBA project so people can't see the
password from there.

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in the left hand window right click on your
workbook name and select VBA project properties, protection, check lock
project for viewing and set a password. Press Alt and Q to close this window
and go back to your workbook and save and close the file. Be aware that this
password can be broken by third party software




--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
N

Nick

Cheers mate that works perfectly. Is there actually any way of getting the
password box to display without enntering the sheet. i.e when Sheet2 is
presses the pass word box is displayed before the sheet can be entered.

Thanks agin mate
 
P

Paul B

Nick, then how about this, it will hide sheet 2 using xl very hidden, you
want see it listed in the unhide menu so most people want know its there.
You will also need to protect the VBA project like before so the password
can't be seen or the sheet made visible from there. When you want to view
the sheet run the unhide_me macro.


Put this in the sheet code

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Visible = xlSheetVeryHidden
On Error GoTo 0
End Sub


And this in a regular module

Sub unhide_me()
Dim MyStr1 As String, MyStr2 As String
MyStr2 = ("123") 'This is the password and it is CASE sensitive
MyStr1 = InputBox("Password Required To Run This Macro")
If MyStr1 = MyStr2 Then
Sheet2.Visible = xlSheetVisible
Sheet2.Select
Else
MsgBox ("Access Denied")
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
N

Nick

Thanks alot mate. I actually sorted the problem by writing
sheets("sheet1").select at the start of the macro. I will try your method as
well as see which works best for my application. Thanks for all the help i'm
very grateful.

Thanks again Paul
 
Top