Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet passwording
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet passwording
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet passwording
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet passwording
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
In Excel, how do you make one whole sheet equal to another. | Excel Discussion (Misc queries) | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |