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
"Paul B" wrote:
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
"Nick" wrote in message
...
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.
"Paul B" wrote:
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" wrote in message
...
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
|