Password Protecting

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

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

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
>
>
>

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
> >
> >
> >

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
> > >
> > >
> > >
>
>
>

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

"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
> > > >
> > > >
> > > >
> >
> >
> >

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

"Paul B" wrote:

> 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
>
> "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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>