Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Is there a way to password protect a worksheet so that the user cannot UNHIDE
the worksheet unless she/he knows the password? Thanks, in advance, for your help. G |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Hi,
There is no real secure way of doing this in Excel but this should deter the average user. Alt+F11 to open VB editor. Double ckick 'ThisWorkbook' and paste the code below in on the right. Change mysheet to the name of the sheet you want to protect. Private Sub Workbook_SheetActivate(ByVal Sh As Object) mysheet = "Sheet1" If ActiveSheet.Name = mysheet Then Application.EnableEvents = False ActiveSheet.Visible = False response = InputBox("Enter password to view sheet") If response = "MyPass" Then Sheets(mysheet).Visible = True Sheets(mysheet).Select End If End If Sheets(mysheet).Visible = True Application.EnableEvents = True End Sub Mike "garyh" wrote: Is there a way to password protect a worksheet so that the user cannot UNHIDE the worksheet unless she/he knows the password? Thanks, in advance, for your help. G |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Launch VBE using Alt+F11.
On the left hand side treeview; click the sheet to be hidden. This will open up the properties for that sheet. Set Visible property to 2- xlVeryHidden. If you need you can password protect VBE from menu Tools|VBA Project properties|Protection If this post helps click Yes -------------- Jacob Skaria "garyh" wrote: Is there a way to password protect a worksheet so that the user cannot UNHIDE the worksheet unless she/he knows the password? Thanks, in advance, for your help. G |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Protect the Workbook, not the Worksheet.
HTH. Best wishes Harald "garyh" wrote in message ... Is there a way to password protect a worksheet so that the user cannot UNHIDE the worksheet unless she/he knows the password? Thanks, in advance, for your help. G |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
slight modification to mikes code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object) mysheet = "Sheet1" If Sh.Name = mysheet Then Application.EnableEvents = False Sh.Visible = False response = InputBox("Enter password to view sheet") If response = "MyPass" Then With Sheets(mysheet) .Visible = True .Select End With Else Sheets(mysheet).Visible = False End If End If Application.EnableEvents = True End Sub -- jb "garyh" wrote: Is there a way to password protect a worksheet so that the user cannot UNHIDE the worksheet unless she/he knows the password? Thanks, in advance, for your help. G |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Thanks for everyone's help. The code works well, but ONLY if the user
selects ENABLE Macros. If the user chooses DISABLE Macros, they can still unhide the sheet. Anything that we can set to turn on Macros, by default (no selection required)? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
There are ways to ensure that the workbook is useless without macros, see
among severar http://www.cpearson.com/excel/EnableMacros.aspx but allow me to repeat: just protect the Workbook with a password, and the user can not unhide sheets. Best wishes Harald "G" wrote in message ... Thanks for everyone's help. The code works well, but ONLY if the user selects ENABLE Macros. If the user chooses DISABLE Macros, they can still unhide the sheet. Anything that we can set to turn on Macros, by default (no selection required)? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Thanks, Harald. I would like to protect the workbook ... can you provide
guidance (e.g., modify your code)? Again, thank you for your assistance. G "Harald Staff" wrote: There are ways to ensure that the workbook is useless without macros, see among severar http://www.cpearson.com/excel/EnableMacros.aspx but allow me to repeat: just protect the Workbook with a password, and the user can not unhide sheets. Best wishes Harald "G" wrote in message ... Thanks for everyone's help. The code works well, but ONLY if the user selects ENABLE Macros. If the user chooses DISABLE Macros, they can still unhide the sheet. Anything that we can set to turn on Macros, by default (no selection required)? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
No code. No macros.
Excel 2007: Review pane, Protect Workbook button, check Structure and provide password. Earlier versions: Menu Tools - Protection - Protect workbook, check Structure and provide password. Best wishes Harald "G" wrote in message ... Thanks, Harald. I would like to protect the workbook ... can you provide guidance (e.g., modify your code)? Again, thank you for your assistance. G "Harald Staff" wrote: There are ways to ensure that the workbook is useless without macros, see among severar http://www.cpearson.com/excel/EnableMacros.aspx but allow me to repeat: just protect the Workbook with a password, and the user can not unhide sheets. Best wishes Harald "G" wrote in message ... Thanks for everyone's help. The code works well, but ONLY if the user selects ENABLE Macros. If the user chooses DISABLE Macros, they can still unhide the sheet. Anything that we can set to turn on Macros, by default (no selection required)? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
Noong Martes, Marso 24 2009 00:27:25 UTC+8, si Harald Staff ay sumulat:
There are ways to ensure that the workbook is useless without macros, see among severar http://www.cpearson.com/excel/EnableMacros.aspx but allow me to repeat: just protect the Workbook with a password, and the user can not unhide sheets. Best wishes Harald "G" wrote in message ... Thanks for everyone's help. The code works well, but ONLY if the user selects ENABLE Macros. If the user chooses DISABLE Macros, they can still unhide the sheet. Anything that we can set to turn on Macros, by default (no selection required)? It is possible to unhide and hide a worksheet using passwords without securing the opening of a workbook. You have to learn VB codes for Excel. You may use the properties xlSheetVeryHidden and xlSheetVisible |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
On Monday, March 23, 2009 8:28:44 AM UTC-4, Harald Staff wrote:
Protect the Workbook, not the Worksheet. HTH. Best wishes Harald "garyh" wrote in message ... Is there a way to password protect a worksheet so that the user cannot UNHIDE the worksheet unless she/he knows the password? Thanks, in advance, for your help. G the below works pretty good for me Private Sub Worksheet_Activate() Dim PASSWORD LINE1: PASSWORD = Application.InputBox("Please enter the password word", "Password") If PASSWORD < "PASSWORD1" Then MsgBox "Please Supply a valid password", vbCritical GoTo LINE1 Else ActiveSheet.Unprotect PASSWORD End If End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
On Thursday, September 27, 2012 2:27:59 AM UTC+8, MDAddio wrote:
On Monday, March 23, 2009 8:28:44 AM UTC-4, Harald Staff wrote: Protect the Workbook, not the Worksheet. HTH. Best wishes Harald "garyh" wrote in message ... Is there a way to password protect a worksheet so that the user cannot UNHIDE the worksheet unless she/he knows the password? Thanks, in advance, for your help. G the below works pretty good for me Private Sub Worksheet_Activate() Dim PASSWORD LINE1: PASSWORD = Application.InputBox("Please enter the password word", "Password") If PASSWORD < "PASSWORD1" Then MsgBox "Please Supply a valid password", vbCritical GoTo LINE1 Else ActiveSheet.Unprotect PASSWORD End If End Sub That does work, but the person can still see the data on the worksheet without having the password. Also, if they don't have the password, there is no way for them to cancel the popup box. You'd be onto a winner if those two bits were added. I'd use it. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password Protect Unhide Worksheet?
That does work, MDAddio, but the person can still see the data on the worksheet without having the password. Also, if they don't have the password, there is no way for them to cancel the popup box. You'd be onto a winner if those two bits were added. I'd use it.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide, unhide column or row after protect worksheet | Excel Discussion (Misc queries) | |||
password protect unhide sheet command | Excel Discussion (Misc queries) | |||
Is there a way that I can password protect a worksheet | Excel Worksheet Functions | |||
Need to password protect work sheet and unhide rows. | Excel Discussion (Misc queries) | |||
Password Protect Worksheet | Excel Programming |