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:=quot;123quot;
Me.Columns.Hidden = True
strPassword = InputBox(quot;Enter password to view this sheetquot;, quot;Password
required !quot;)
If strPassword = quot;quot; Then
Me.Previous.Select
Exit Sub
ElseIf strPassword lt;gt; quot;123quot; Then
MsgBox quot;Password Incorrectquot;, , quot;Wrong passwordquot;
Me.Previous.Select
Exit Sub
Else
Me.Unprotect Password:=quot;123quot;
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 quot;Filequot; 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 amp; 2003
quot;Deanquot; gt; wrote in message
...
gt; Is it possible to password protect a sheet in Excel so that if a user
clicks
gt; the Tab they are asked for the password before the screen is displayed?
gt;
gt; VBA coding could be a possability?
gt;
gt; Thanks Dean
Thank you, Thank you and ones again....Thanks you.
Dean
quot;Paul Bquot; wrote:
gt; Dean, here is some code that will do it, don't remember where it came from,
gt; password set to 123
gt;
gt; Private Sub Worksheet_Activate()
gt; Dim strPassword As String
gt; On Error Resume Next
gt; Me.Protect Password:=quot;123quot;
gt; Me.Columns.Hidden = True
gt;
gt; strPassword = InputBox(quot;Enter password to view this sheetquot;, quot;Password
gt; required !quot;)
gt;
gt; If strPassword = quot;quot; Then
gt; Me.Previous.Select
gt; Exit Sub
gt; ElseIf strPassword lt;gt; quot;123quot; Then
gt; MsgBox quot;Password Incorrectquot;, , quot;Wrong passwordquot;
gt; Me.Previous.Select
gt; Exit Sub
gt; Else
gt; Me.Unprotect Password:=quot;123quot;
gt; Me.Columns.Hidden = False
gt; End If
gt;
gt; On Error GoTo 0
gt; End Sub
gt;
gt; Private Sub Worksheet_Deactivate()
gt; On Error Resume Next
gt; Me.Columns.Hidden = True
gt; On Error GoTo 0
gt; End Sub
gt;
gt; To put in this macro right click on the worksheet tab and view code, in the
gt; window that opens paste this code, press Alt and Q to close this window and
gt; go back to your workbook. If you are using excel 2000 or newer you may have
gt; to change the macro security settings to get the macro to run. To change the
gt; security settings go to tools, macro, security, security level and set it to
gt; medium
gt;
gt; To change the security settings go to tools, macro, security, security level
gt; and set it to medium
gt;
gt; You will also need to password protect your VBA project so no one can see
gt; the password there
gt;
gt; To protect the VBA project, from your workbook right-click the workbook's
gt; icon and pick View Code. This icon is to the left of the quot;Filequot; menu this
gt; will open the VBA editor, in Project Explorer right click on your workbook
gt; name, if you don't see it press CTRL r to open the Project Explorer then
gt; select VBA project properties, protection, check lock project for viewing
gt; and set a password. Press Alt and Q to close this window and go back to your
gt; workbook and save and close the file. Be aware that this password can be
gt; broken by third party software
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;Deanquot; gt; wrote in message
gt; ...
gt; gt; Is it possible to password protect a sheet in Excel so that if a user
gt; clicks
gt; gt; the Tab they are asked for the password before the screen is displayed?
gt; gt;
gt; gt; VBA coding could be a possability?
gt; gt;
gt; gt; Thanks Dean
gt;
gt;
gt;
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 amp; 2003
quot;Deanquot; gt; wrote in message
...
gt; Thank you, Thank you and ones again....Thanks you.
gt;
gt; Dean
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt; gt; Dean, here is some code that will do it, don't remember where it came
from,
gt; gt; password set to 123
gt; gt;
gt; gt; Private Sub Worksheet_Activate()
gt; gt; Dim strPassword As String
gt; gt; On Error Resume Next
gt; gt; Me.Protect Password:=quot;123quot;
gt; gt; Me.Columns.Hidden = True
gt; gt;
gt; gt; strPassword = InputBox(quot;Enter password to view this sheetquot;, quot;Password
gt; gt; required !quot;)
gt; gt;
gt; gt; If strPassword = quot;quot; Then
gt; gt; Me.Previous.Select
gt; gt; Exit Sub
gt; gt; ElseIf strPassword lt;gt; quot;123quot; Then
gt; gt; MsgBox quot;Password Incorrectquot;, , quot;Wrong passwordquot;
gt; gt; Me.Previous.Select
gt; gt; Exit Sub
gt; gt; Else
gt; gt; Me.Unprotect Password:=quot;123quot;
gt; gt; Me.Columns.Hidden = False
gt; gt; End If
gt; gt;
gt; gt; On Error GoTo 0
gt; gt; End Sub
gt; gt;
gt; gt; Private Sub Worksheet_Deactivate()
gt; gt; On Error Resume Next
gt; gt; Me.Columns.Hidden = True
gt; gt; On Error GoTo 0
gt; gt; End Sub
gt; gt;
gt; gt; To put in this macro right click on the worksheet tab and view code, in
the
gt; gt; window that opens paste this code, press Alt and Q to close this window
and
gt; gt; go back to your workbook. If you are using excel 2000 or newer you may
have
gt; gt; to change the macro security settings to get the macro to run. To change
the
gt; gt; security settings go to tools, macro, security, security level and set
it to
gt; gt; medium
gt; gt;
gt; gt; To change the security settings go to tools, macro, security, security
level
gt; gt; and set it to medium
gt; gt;
gt; gt; You will also need to password protect your VBA project so no one can
see
gt; gt; the password there
gt; gt;
gt; gt; To protect the VBA project, from your workbook right-click the
workbook's
gt; gt; icon and pick View Code. This icon is to the left of the quot;Filequot; menu
this
gt; gt; will open the VBA editor, in Project Explorer right click on your
workbook
gt; gt; name, if you don't see it press CTRL r to open the Project Explorer
then
gt; gt; select VBA project properties, protection, check lock project for
viewing
gt; gt; and set a password. Press Alt and Q to close this window and go back to
your
gt; gt; workbook and save and close the file. Be aware that this password can be
gt; gt; broken by third party software
gt; gt; --
gt; gt; Paul B
gt; gt; Always backup your data before trying something new
gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; Feedback on answers is always appreciated!
gt; gt; Using Excel 2002 amp; 2003
gt; gt;
gt; gt; quot;Deanquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Is it possible to password protect a sheet in Excel so that if a user
gt; gt; clicks
gt; gt; gt; the Tab they are asked for the password before the screen is
displayed?
gt; gt; gt;
gt; gt; gt; VBA coding could be a possability?
gt; gt; gt;
gt; gt; gt; Thanks Dean
gt; gt;
gt; gt;
gt; gt;
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
quot;Paul Bquot; wrote:
gt; Your welcome
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;Deanquot; gt; wrote in message
gt; ...
gt; gt; Thank you, Thank you and ones again....Thanks you.
gt; gt;
gt; gt; Dean
gt; gt;
gt; gt; quot;Paul Bquot; wrote:
gt; gt;
gt; gt; gt; Dean, here is some code that will do it, don't remember where it came
gt; from,
gt; gt; gt; password set to 123
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Activate()
gt; gt; gt; Dim strPassword As String
gt; gt; gt; On Error Resume Next
gt; gt; gt; Me.Protect Password:=quot;123quot;
gt; gt; gt; Me.Columns.Hidden = True
gt; gt; gt;
gt; gt; gt; strPassword = InputBox(quot;Enter password to view this sheetquot;, quot;Password
gt; gt; gt; required !quot;)
gt; gt; gt;
gt; gt; gt; If strPassword = quot;quot; Then
gt; gt; gt; Me.Previous.Select
gt; gt; gt; Exit Sub
gt; gt; gt; ElseIf strPassword lt;gt; quot;123quot; Then
gt; gt; gt; MsgBox quot;Password Incorrectquot;, , quot;Wrong passwordquot;
gt; gt; gt; Me.Previous.Select
gt; gt; gt; Exit Sub
gt; gt; gt; Else
gt; gt; gt; Me.Unprotect Password:=quot;123quot;
gt; gt; gt; Me.Columns.Hidden = False
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; On Error GoTo 0
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Deactivate()
gt; gt; gt; On Error Resume Next
gt; gt; gt; Me.Columns.Hidden = True
gt; gt; gt; On Error GoTo 0
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; To put in this macro right click on the worksheet tab and view code, in
gt; the
gt; gt; gt; window that opens paste this code, press Alt and Q to close this window
gt; and
gt; gt; gt; go back to your workbook. If you are using excel 2000 or newer you may
gt; have
gt; gt; gt; to change the macro security settings to get the macro to run. To change
gt; the
gt; gt; gt; security settings go to tools, macro, security, security level and set
gt; it to
gt; gt; gt; medium
gt; gt; gt;
gt; gt; gt; To change the security settings go to tools, macro, security, security
gt; level
gt; gt; gt; and set it to medium
gt; gt; gt;
gt; gt; gt; You will also need to password protect your VBA project so no one can
gt; see
gt; gt; gt; the password there
gt; gt; gt;
gt; gt; gt; To protect the VBA project, from your workbook right-click the
gt; workbook's
gt; gt; gt; icon and pick View Code. This icon is to the left of the quot;Filequot; menu
gt; this
gt; gt; gt; will open the VBA editor, in Project Explorer right click on your
gt; workbook
gt; gt; gt; name, if you don't see it press CTRL r to open the Project Explorer
gt; then
gt; gt; gt; select VBA project properties, protection, check lock project for
gt; viewing
gt; gt; gt; and set a password. Press Alt and Q to close this window and go back to
gt; your
gt; gt; gt; workbook and save and close the file. Be aware that this password can be
gt; gt; gt; broken by third party software
gt; gt; gt; --
gt; gt; gt; Paul B
gt; gt; gt; Always backup your data before trying something new
gt; gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; gt; Feedback on answers is always appreciated!
gt; gt; gt; Using Excel 2002 amp; 2003
gt; gt; gt;
gt; gt; gt; quot;Deanquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Is it possible to password protect a sheet in Excel so that if a user
gt; gt; gt; clicks
gt; gt; gt; gt; the Tab they are asked for the password before the screen is
gt; displayed?
gt; gt; gt; gt;
gt; gt; gt; gt; VBA coding could be a possability?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks Dean
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;
- May 16 Wed 2007 20:37
Sheet passwording
close
全站熱搜
留言列表
發表留言