Sheet passwording

Is it possible to password protect a sheet in Excel so that if a user clicks
the Tab they are asked for the password before the screen is displayed?

VBA coding could be a possability?

Thanks Dean

Dean, here is some code that will do it, don't remember where it came from,
password set to 123

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", "Password
required !")

If strPassword = "" Then
Me.Previous.Select
Exit Sub
ElseIf strPassword <> "123" Then
MsgBox "Password Incorrect", , "Wrong password"
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

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. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

To change the security settings go to tools, macro, security, security level
and set it to medium

You will also need to password protect your VBA project so no one can see
the password 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 Project Explorer right click on your workbook
name, if you don't see it press CTRL + r to open the Project Explorer then
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

"Dean" <Dean@discussions.microsoft.com> wrote in message
news:5BF3D0F0-5C30-457F-9011-E5083B716575@microsoft.com...
> Is it possible to password protect a sheet in Excel so that if a user
clicks
> the Tab they are asked for the password before the screen is displayed?
>
> VBA coding could be a possability?
>
> Thanks Dean

Thank you, Thank you and ones again....Thanks you.

Dean

"Paul B" wrote:

> Dean, here is some code that will do it, don't remember where it came from,
> password set to 123
>
> 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", "Password
> required !")
>
> If strPassword = "" Then
> Me.Previous.Select
> Exit Sub
> ElseIf strPassword <> "123" Then
> MsgBox "Password Incorrect", , "Wrong password"
> 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
>
> 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. If you are using excel 2000 or newer you may have
> to change the macro security settings to get the macro to run. To change the
> security settings go to tools, macro, security, security level and set it to
> medium
>
> To change the security settings go to tools, macro, security, security level
> and set it to medium
>
> You will also need to password protect your VBA project so no one can see
> the password 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 Project Explorer right click on your workbook
> name, if you don't see it press CTRL + r to open the Project Explorer then
> 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
>
> "Dean" <Dean@discussions.microsoft.com> wrote in message
> news:5BF3D0F0-5C30-457F-9011-E5083B716575@microsoft.com...
> > Is it possible to password protect a sheet in Excel so that if a user
> clicks
> > the Tab they are asked for the password before the screen is displayed?
> >
> > VBA coding could be a possability?
> >
> > Thanks Dean
>
>
>

Your welcome

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

"Dean" <Dean@discussions.microsoft.com> wrote in message
news:19A8F3B0-D689-4406-8BF3-E7EA48D609D3@microsoft.com...
> Thank you, Thank you and ones again....Thanks you.
>
> Dean
>
> "Paul B" wrote:
>
> > Dean, here is some code that will do it, don't remember where it came
from,
> > password set to 123
> >
> > 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", "Password
> > required !")
> >
> > If strPassword = "" Then
> > Me.Previous.Select
> > Exit Sub
> > ElseIf strPassword <> "123" Then
> > MsgBox "Password Incorrect", , "Wrong password"
> > 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
> >
> > 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. If you are using excel 2000 or newer you may
have
> > to change the macro security settings to get the macro to run. To change
the
> > security settings go to tools, macro, security, security level and set
it to
> > medium
> >
> > To change the security settings go to tools, macro, security, security
level
> > and set it to medium
> >
> > You will also need to password protect your VBA project so no one can
see
> > the password 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 Project Explorer right click on your
workbook
> > name, if you don't see it press CTRL + r to open the Project Explorer
then
> > 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
> >
> > "Dean" <Dean@discussions.microsoft.com> wrote in message
> > news:5BF3D0F0-5C30-457F-9011-E5083B716575@microsoft.com...
> > > Is it possible to password protect a sheet in Excel so that if a user
> > clicks
> > > the Tab they are asked for the password before the screen is
displayed?
> > >
> > > VBA coding could be a possability?
> > >
> > > Thanks Dean
> >
> >
> >

Paul,
The code works beautifully, EXCEPT that when I click on the tab I want to
view, I am prompted for a password, but the sheet is displayed also (I can't
edit it, but I can also move the password prompt to see any part of it I want)
Any way to hide the display? I just don't know enough about VB to know how
to change that property or setting.
Thanks,
Jen

"Paul B" wrote:

> Your welcome
>
> --
> 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
>
> "Dean" <Dean@discussions.microsoft.com> wrote in message
> news:19A8F3B0-D689-4406-8BF3-E7EA48D609D3@microsoft.com...
> > Thank you, Thank you and ones again....Thanks you.
> >
> > Dean
> >
> > "Paul B" wrote:
> >
> > > Dean, here is some code that will do it, don't remember where it came
> from,
> > > password set to 123
> > >
> > > 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", "Password
> > > required !")
> > >
> > > If strPassword = "" Then
> > > Me.Previous.Select
> > > Exit Sub
> > > ElseIf strPassword <> "123" Then
> > > MsgBox "Password Incorrect", , "Wrong password"
> > > 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
> > >
> > > 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. If you are using excel 2000 or newer you may
> have
> > > to change the macro security settings to get the macro to run. To change
> the
> > > security settings go to tools, macro, security, security level and set
> it to
> > > medium
> > >
> > > To change the security settings go to tools, macro, security, security
> level
> > > and set it to medium
> > >
> > > You will also need to password protect your VBA project so no one can
> see
> > > the password 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 Project Explorer right click on your
> workbook
> > > name, if you don't see it press CTRL + r to open the Project Explorer
> then
> > > 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
> > >
> > > "Dean" <Dean@discussions.microsoft.com> wrote in message
> > > news:5BF3D0F0-5C30-457F-9011-E5083B716575@microsoft.com...
> > > > Is it possible to password protect a sheet in Excel so that if a user
> > > clicks
> > > > the Tab they are asked for the password before the screen is
> displayed?
> > > >
> > > > VBA coding could be a possability?
> > > >
> > > > Thanks Dean
> > >
> > >
> > >
>
>
>