LinkBack URL
About LinkBacksNick, 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
"Nick" <Nick@discussions.microsoft.com> wrote in message
news:47C3FFDC-F312-443F-975A-35934FCF6356@microsoft.com...
> 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" <Nick@discussions.microsoft.com> wrote in message
> > news:9D30364D-B7A4-4143-9112-FA44052D9696@microsoft.com...
> > > 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" <Nick@discussions.microsoft.com> wrote in message
> > > > news:9317C7B9-C451-4666-AE36-C0C70F3AB2EC@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >
Register To Reply
Bookmarks