Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Dear expert,
I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi Freshman,
In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have, however, is that your users will forget to re-protect the worksheet before saving & closing the workbook. For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password for it. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Set ToolsProtectionSheet protection individually for each sheets with
different passwords! Regards, Stefi Freshman ezt *rta: Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi macropod,
I'm interested to have a macro to protect worksheets with different passwords, then I can assign the passwords to each individual user. May I have the code as I'm a VBA idiot. Thanks. "macropod" wrote: Hi Freshman, In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have, however, is that your users will forget to re-protect the worksheet before saving & closing the workbook. For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password for it. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Thanks Stefi.
"Stefi" wrote: Set ToolsProtectionSheet protection individually for each sheets with different passwords! Regards, Stefi Freshman ezt *rta: Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi Freshman,
Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module. Option Explicit Dim User As String Dim UPwd As String Dim WPwd As String Dim Err As Boolean Dim wsSheet As Worksheet Dim wsActvSht As Worksheet Private Sub Workbook_Open() WPwd = "" 'Inset the Workbook Password between the double quotes Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd wsActvSht.Activate For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = wsActvSht.Name Then .Visible = xlSheetVisible Else .Visible = xlSheetVeryHidden End If End With Next wsSheet Restart: User = InputBox("Please Input your Workbook Username") UPwd = InputBox("Please Input your Workbook Password") For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then On Error GoTo Restart If .ProtectContents = True Then .Unprotect UPwd .Visible = xlSheetVisible .Activate Exit Sub End If End With Next wsSheet ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits. The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc. You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll leave that to you. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... Hi macropod, I'm interested to have a macro to protect worksheets with different passwords, then I can assign the passwords to each individual user. May I have the code as I'm a VBA idiot. Thanks. "macropod" wrote: Hi Freshman, In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have, however, is that your users will forget to re-protect the worksheet before saving & closing the workbook. For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password for it. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
This is how I do it...
Place the following code in a Sheet (not a module) Basically, right-click the sheet and click View Code Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password to Unhide Sheet", "Unhide Sheet...") If i_pwd = "" Then Exit Sub End If '#1 Select Case (i_pwd) Case Is = "showmktg" Worksheets("mktg1").Visible = True Worksheets("mktg2").Visible = True Worksheets("mktg2").Visible = True Sheets("mktg1").Select '#2 Case Is = "showsales" Worksheets("sales1").Visible = True Worksheets("sales2").Visible = True Worksheets("sales3").Visible = True Sheets("sales1").Select '#3 Case Is = "showall" Call ShowSheets Case Else MsgBox "Incorrect password; no action taken.", vbInformation, _ "Unhide Sheet..." End Select Exit Sub End Sub Sub ShowSheets() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets If (sh.Name) < "Password" Then If sh.Visible = xlSheetVeryHidden Then sh.Visible = True End If End If Next sh End Sub Place following code in sheet called ThisWorkbook Private Sub Workbook_BeforeClose(Cancel As Boolean) Call HideSheets End Sub Regards, Ryan--- -- RyGuy "Freshman" wrote: Thanks Stefi. "Stefi" wrote: Set ToolsProtectionSheet protection individually for each sheets with different passwords! Regards, Stefi Freshman ezt *rta: Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if i want multiple sheets to be visible untill i close workbook
hi.
the first code by macropod is awesome.. what changes will i have to be made if i want the multiple worksheet to remain visible untill i close my workbook. For ex. i opened one worksheet after entering password but i want to open another worksheet by entering different password . now i want them to remain visible till i close the workbook amutak macropod wrote: Hi Freshman,Here's some code to get you started. 28-Jul-08 Hi Freshman, Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module. Option Explicit Dim User As String Dim UPwd As String Dim WPwd As String Dim Err As Boolean Dim wsSheet As Worksheet Dim wsActvSht As Worksheet Private Sub Workbook_Open() WPwd = "" 'Inset the Workbook Password between the double quotes Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd wsActvSht.Activate For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = wsActvSht.Name Then .Visible = xlSheetVisible Else .Visible = xlSheetVeryHidden End If End With Next wsSheet Restart: User = InputBox("Please Input your Workbook Username") UPwd = InputBox("Please Input your Workbook Password") For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then On Error GoTo Restart If .ProtectContents = True Then .Unprotect UPwd .Visible = xlSheetVisible .Activate Exit Sub End If End With Next wsSheet ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits. The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc. You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll leave that to you. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... Previous Posts In This Thread: On Monday, July 28, 2008 2:59 AM Freshma wrote: Different password for different worksheet Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. On Monday, July 28, 2008 3:24 AM macropod wrote: Hi Freshman,In short, yes, It's possible. Hi Freshman, In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have, however, is that your users will forget to re-protect the worksheet before saving & closing the workbook. For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password for it. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... On Monday, July 28, 2008 3:30 AM Stef wrote: Set ToolsProtectionSheet protection individually for each sheets with Set ToolsProtectionSheet protection individually for each sheets with different passwords! Regards, Stefi ???Freshman??? ezt ??rta: On Monday, July 28, 2008 3:43 AM Freshma wrote: Hi macropod,I'm interested to have a macro to protect worksheets with Hi macropod, I'm interested to have a macro to protect worksheets with different passwords, then I can assign the passwords to each individual user. May I have the code as I'm a VBA idiot. Thanks. "macropod" wrote: On Monday, July 28, 2008 3:44 AM Freshma wrote: Different password for different worksheet Thanks Stefi. "Stefi" wrote: On Monday, July 28, 2008 5:46 AM macropod wrote: Hi Freshman,Here's some code to get you started. Hi Freshman, Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module. Option Explicit Dim User As String Dim UPwd As String Dim WPwd As String Dim Err As Boolean Dim wsSheet As Worksheet Dim wsActvSht As Worksheet Private Sub Workbook_Open() WPwd = "" 'Inset the Workbook Password between the double quotes Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd wsActvSht.Activate For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = wsActvSht.Name Then .Visible = xlSheetVisible Else .Visible = xlSheetVeryHidden End If End With Next wsSheet Restart: User = InputBox("Please Input your Workbook Username") UPwd = InputBox("Please Input your Workbook Password") For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then On Error GoTo Restart If .ProtectContents = True Then .Unprotect UPwd .Visible = xlSheetVisible .Activate Exit Sub End If End With Next wsSheet ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits. The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc. You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll leave that to you. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... On Tuesday, July 29, 2008 1:24 PM ryguy727 wrote: This is how I do it... This is how I do it... Place the following code in a Sheet (not a module) Basically, right-click the sheet and click ???View Code??? Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password to Unhide Sheet", "Unhide Sheet...") If i_pwd = "" Then Exit Sub End If '#1 Select Case (i_pwd) Case Is = "showmktg" Worksheets("mktg1").Visible = True Worksheets("mktg2").Visible = True Worksheets("mktg2").Visible = True Sheets("mktg1").Select '#2 Case Is = "showsales" Worksheets("sales1").Visible = True Worksheets("sales2").Visible = True Worksheets("sales3").Visible = True Sheets("sales1").Select '#3 Case Is = "showall" Call ShowSheets Case Else MsgBox "Incorrect password; no action taken.", vbInformation, _ "Unhide Sheet..." End Select Exit Sub End Sub Sub ShowSheets() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets If (sh.Name) < "Password" Then If sh.Visible = xlSheetVeryHidden Then sh.Visible = True End If End If Next sh End Sub Place following code in sheet called ???ThisWorkbook??? Private Sub Workbook_BeforeClose(Cancel As Boolean) Call HideSheets End Sub Regards, Ryan--- -- RyGuy "Freshman" wrote: EggHeadCafe - Software Developer Portal of Choice SharePoint Site Provisioning ? The Real World http://www.eggheadcafe.com/tutorials...provision.aspx |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Dear expert,
I am using MS Excel 2007. I want to create a workbook with different worksheets for different users to be able to view and edit the content on his/her designated worksheet. i.e. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can view & edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to view and protec. Is it possible? Please kindly advise. Thanks in advance. "ryguy7272" wrote: This is how I do it... Place the following code in a Sheet (not a module) Basically, right-click the sheet and click View Code Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password to Unhide Sheet", "Unhide Sheet...") If i_pwd = "" Then Exit Sub End If '#1 Select Case (i_pwd) Case Is = "showmktg" Worksheets("mktg1").Visible = True Worksheets("mktg2").Visible = True Worksheets("mktg2").Visible = True Sheets("mktg1").Select '#2 Case Is = "showsales" Worksheets("sales1").Visible = True Worksheets("sales2").Visible = True Worksheets("sales3").Visible = True Sheets("sales1").Select '#3 Case Is = "showall" Call ShowSheets Case Else MsgBox "Incorrect password; no action taken.", vbInformation, _ "Unhide Sheet..." End Select Exit Sub End Sub Sub ShowSheets() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets If (sh.Name) < "Password" Then If sh.Visible = xlSheetVeryHidden Then sh.Visible = True End If End If Next sh End Sub Place following code in sheet called ThisWorkbook Private Sub Workbook_BeforeClose(Cancel As Boolean) Call HideSheets End Sub Regards, Ryan--- -- RyGuy "Freshman" wrote: Thanks Stefi. "Stefi" wrote: Set ToolsProtectionSheet protection individually for each sheets with different passwords! Regards, Stefi Freshman ezt *rta: Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Keeping in mind that internal security is very weak in Excel.
Requires VBA code and some passwords or login names. Sample code....................... Note: the following is contingent upon users enabling macros. If they don't only the "Dummy" sheet will be visible with a large message stating "By disabling macros you have rendered this workbook unusuable. Please close and re-open with macros enabled" I assume you are on a network(LAN) with users logging into the system. I would set it up so that whichever user's login name is flagged, all sheets except that user would be hidden. No password to open the workbook or sheet protection, just code to make a user's sheet visible. In the Thisworkbook Module.................... Private Sub Workbook_Open() Dim pword As String On Error GoTo endit Select Case Environ("Username") 'if a login is not used change to 'pword = InputBox("Enter Your Password") 'Select Case pword Case Is = "Gord": Sheets("Gordsheet").Visible = True Case Is = "Pete": Sheets("Petesheet").Visible = True End Select Sheets("Dummy").Visible = False Exit Sub endit: MsgBox "Incorrect Password" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sht As Worksheet Application.ScreenUpdating = False Sheets("Dummy").Visible = xlSheetVisible For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True ThisWorkbook.Save End Sub To allow you to see all sheets and edit them. In a general module............... Sub UnHideAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Visible = True Next n Application.ScreenUpdating = True End Sub Naturally you want all this code invisible to the users. Right-click on the workbook/project in VBE and select VBAProject Properties and "Lock project for viewing" Enter a password. Gord Dibben MS Excel MVP On Fri, 2 Apr 2010 23:59:01 -0700, Minu wrote: Thank you Don. But how to set individual excel sheet passwords for viewing and editing. i.e only desginated user to view and edit. Appreciate some help in this matter. "Don Guillett" wrote: Excel sheet passwords are easily bypassed. -- Don Guillett Microsoft MVP Excel SalesAid Software "Minu" wrote in message ... Dear expert, I am using MS Excel 2007. I want to create a workbook with different worksheets for different users to be able to view and edit the content on his/her designated worksheet. i.e. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can view & edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to view and protec. Is it possible? Please kindly advise. Thanks in advance. "ryguy7272" wrote: This is how I do it... Place the following code in a Sheet (not a module) Basically, right-click the sheet and click View Code Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password to Unhide Sheet", "Unhide Sheet...") If i_pwd = "" Then Exit Sub End If '#1 Select Case (i_pwd) Case Is = "showmktg" Worksheets("mktg1").Visible = True Worksheets("mktg2").Visible = True Worksheets("mktg2").Visible = True Sheets("mktg1").Select '#2 Case Is = "showsales" Worksheets("sales1").Visible = True Worksheets("sales2").Visible = True Worksheets("sales3").Visible = True Sheets("sales1").Select '#3 Case Is = "showall" Call ShowSheets Case Else MsgBox "Incorrect password; no action taken.", vbInformation, _ "Unhide Sheet..." End Select Exit Sub End Sub Sub ShowSheets() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets If (sh.Name) < "Password" Then If sh.Visible = xlSheetVeryHidden Then sh.Visible = True End If End If Next sh End Sub Place following code in sheet called ThisWorkbook Private Sub Workbook_BeforeClose(Cancel As Boolean) Call HideSheets End Sub Regards, Ryan--- -- RyGuy "Freshman" wrote: Thanks Stefi. "Stefi" wrote: Set ToolsProtectionSheet protection individually for each sheets with different passwords! Regards, Stefi Freshman ezt rta: Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. . |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
On Monday, 28 July 2008 12:54:29 UTC+5:30, macropod wrote:
Hi Freshman, In short, yes, It's possible. For any given sheet look under tools|Protection|Protect Sheet. The problem you're likely to have, however, is that your users will forget to re-protect the worksheet before saving & closing the workbook. For something more sophisticated, you could write a macro that prevents a user accessing a worksheet unless they provide a password for it. -- Cheers macropod [MVP - Microsoft Word] "Freshman" wrote in message ... Dear expert, I want to create a workbook with different worksheets for different users. For security purpose, I want different worksheet has a different password to protect so that the only authorized user can edit the content on his/her designated worksheet. For example, sheet "marketing" has a password "1234" and sheet "sales" has a password "1235" to protect. Is it possible? Please kindly advise. Thanks in advance. I tried this, but is there an option to place a password? I could just see set permissions, wherein only permitted people can use the sheet. But couldnt find any password set up |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi Macropod,
Could you please help me with the same code. In case I want to create a result sheet but don't want a student to see other student's marks. So, how can I add different passwords for different students. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi Macropod,
Could you please help me with the same code. In case I want to create a result sheet but don't want a student to see other student's marks. So, how can I add different passwords for different students. Are you trusting that the students won't share passwords? Instead of a 'results sheet', why not just display the results for the student in a msgbox -OR- a designated field on the form where the StudentName:Password gets input? Otherwise, explain how you want this 'results sheet' approach to work! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi Gary,
Not able to reply to the email that you sent. So asking here. Thank you for replying. I have no other option but to trust the integrity. So, I used your below code to create a test file and I kind of thought it as best option instead of manually copy pasting results in email or providing hard copies. However, i am not able to close the file as it gives me error message. Step 1: Input Username or name of Worksheet - Name of Student. Step 2- Input Unique password for each worksheet which is each password for each student Step 3- Result. According to username password it will open respective sheet only. Step 4- Close the workbook. Here I am facing an error without saving anything. It Highlights this part of the code. Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True I need your help with this code. I think the code is working well after the error will be rectified just that in the code I am not able to set unique password for unique username(worksheet name). I can only set a single password for all username using this code. Also, how can I have a blanket access to add marks of students if that is not possible then I will add one raw data worksheet and put in formulaes in students worksheet to vlookup the results from raw data worksheet.. Does that sound good? Again, thank you for helping me out on the code. I really appreciate this. On Monday, July 28, 2008 at 3:16:58 PM UTC+5:30, macropod wrote: Hi Freshman, Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module. Option Explicit Dim User As String Dim UPwd As String Dim WPwd As String Dim Err As Boolean Dim wsSheet As Worksheet Dim wsActvSht As Worksheet Private Sub Workbook_Open() WPwd = "" 'Inset the Workbook Password between the double quotes Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd wsActvSht.Activate For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = wsActvSht.Name Then .Visible = xlSheetVisible Else .Visible = xlSheetVeryHidden End If End With Next wsSheet Restart: User = InputBox("Please Input your Workbook Username") UPwd = InputBox("Please Input your Workbook Password") For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then On Error GoTo Restart If .ProtectContents = True Then .Unprotect UPwd .Visible = xlSheetVisible .Activate Exit Sub End If End With Next wsSheet ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits. The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc. You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll leave that to you. -- Cheers macropod [MVP - Microsoft Word] |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different password for different worksheet
Hi Gary,
Not able to reply to the email that you sent. So asking here. Thank you for replying. I have no other option but to trust the integrity. So, I used your below code to create a test file and I kind of thought it as best option instead of manually copy pasting results in email or providing hard copies. However, i am not able to close the file as it gives me error message. Step 1: Input Username or name of Worksheet - Name of Student. Step 2- Input Unique password for each worksheet which is each password for each student Step 3- Result. According to username password it will open respective sheet only. Step 4- Close the workbook. Here I am facing an error without saving anything. It Highlights this part of the code. Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True I need your help with this code. I think the code is working well after the error will be rectified just that in the code I am not able to set unique password for unique username(worksheet name). I can only set a single password for all username using this code. Also, how can I have a blanket access to add marks of students if that is not possible then I will add one raw data worksheet and put in formulaes in students worksheet to vlookup the results from raw data worksheet. Does that sound good? Again, thank you for helping me out on the code. I really appreciate this. On Monday, July 28, 2008 at 3:16:58 PM UTC+5:30, macropod wrote: Hi Freshman, Here's some code to get you started. It goes into your workbook's 'ThisWorkbook' vba module. Option Explicit Dim User As String Dim UPwd As String Dim WPwd As String Dim Err As Boolean Dim wsSheet As Worksheet Dim wsActvSht As Worksheet Private Sub Workbook_Open() WPwd = "" 'Inset the Workbook Password between the double quotes Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd wsActvSht.Activate For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = wsActvSht.Name Then .Visible = xlSheetVisible Else .Visible = xlSheetVeryHidden End If End With Next wsSheet Restart: User = InputBox("Please Input your Workbook Username") UPwd = InputBox("Please Input your Workbook Password") For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then On Error GoTo Restart If .ProtectContents = True Then .Unprotect UPwd .Visible = xlSheetVisible .Activate Exit Sub End If End With Next wsSheet ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd For Each wsSheet In ActiveWorkbook.Worksheets With wsSheet If .Name = User Then .Protect UPwd If .Name < wsActvSht.Name Then .Visible = xlSheetVeryHidden End With Next wsSheet wsActvSht.Activate ThisWorkbook.Protect Password:=WPwd, Structu=True, Windows:=True End Sub The code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits. The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc. You'll probably want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible. I'll leave that to you. -- Cheers macropod [MVP - Microsoft Word] Can you upload the file to an online place I can download it from so I can see exactly what you are doing? FWIW: I have a StudentGradesManager addin that handles this nicely, but in a different way; - 1 sheet per class, student records are filtered for viewing. In your case of 1 sheet per student -OR- a separate 'report' sheet per query, a secure solution should be fairly simple to implement if I know the structure of your file. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet password | Excel Worksheet Functions | |||
password a worksheet | New Users to Excel | |||
Worksheet password | Excel Discussion (Misc queries) | |||
I need to unprotect a worksheet but I don't have the password. | Excel Discussion (Misc queries) | |||
Can you password an Excel worksheet, if so how? | Excel Worksheet Functions |