If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Password protect sheet from viewing
I'm using Excel 2007
I have a team of sales individuals and have created a workbook which contains a worksheet for each individual. When they open it I only want them to be able to view their corresponding worksheet. I would like them to do the following: 1. Open workbook 2. Enter password to see their corresponding worksheet. 2. Enter data 4. Save workbook 5. Send back to me. |
#2
|
|||
|
|||
Password protect sheet from viewing
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, just code to make a user's sheet visible. In the Thisworkbook Module.................... Private Sub Workbook_Open() Dim pword As String 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 Sheets("Dummy").Visible = False Case Is = "Pete": Sheets("Petesheet").Visible = True Sheets("Dummy").Visible = False End Select 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 Thu, 26 Mar 2009 14:21:01 -0700, Enrique wrote: I'm using Excel 2007 I have a team of sales individuals and have created a workbook which contains a worksheet for each individual. When they open it I only want them to be able to view their corresponding worksheet. I would like them to do the following: 1. Open workbook 2. Enter password to see their corresponding worksheet. 2. Enter data 4. Save workbook 5. Send back to me. |
#3
|
|||
|
|||
Password protect sheet from viewing
I love your idea, checking the user name they've logged in with would be
awesome. The problem is I can't seem to make it work. I am new to Visual Basic and have tried every which way to make your program work. I was wondering if you could give me an exact example of what the code would look like with three users. The user names would be bhall, gclinton, aburton. The sheets in the workbook are named after their user names. Also, is there anything I will need to have the IT guys do to make this work on the network? "Gord Dibben" wrote: 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, just code to make a user's sheet visible. In the Thisworkbook Module.................... Private Sub Workbook_Open() Dim pword As String 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 Sheets("Dummy").Visible = False Case Is = "Pete": Sheets("Petesheet").Visible = True Sheets("Dummy").Visible = False End Select 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 Thu, 26 Mar 2009 14:21:01 -0700, Enrique wrote: I'm using Excel 2007 I have a team of sales individuals and have created a workbook which contains a worksheet for each individual. When they open it I only want them to be able to view their corresponding worksheet. I would like them to do the following: 1. Open workbook 2. Enter password to see their corresponding worksheet. 2. Enter data 4. Save workbook 5. Send back to me. |
#4
|
|||
|
|||
Password protect sheet from viewing
This was extremely helpful. It took me awhile to get it working but now it
works like a charm. Thanks Mr. Dibben (Excel MVP) "Gord Dibben" wrote: 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, just code to make a user's sheet visible. In the Thisworkbook Module.................... Private Sub Workbook_Open() Dim pword As String 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 Sheets("Dummy").Visible = False Case Is = "Pete": Sheets("Petesheet").Visible = True Sheets("Dummy").Visible = False End Select 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 Thu, 26 Mar 2009 14:21:01 -0700, Enrique wrote: I'm using Excel 2007 I have a team of sales individuals and have created a workbook which contains a worksheet for each individual. When they open it I only want them to be able to view their corresponding worksheet. I would like them to do the following: 1. Open workbook 2. Enter password to see their corresponding worksheet. 2. Enter data 4. Save workbook 5. Send back to me. |
#5
|
|||
|
|||
Password protect sheet from viewing
Happy to help.
Thanks for the feedback. Gord On Mon, 13 Apr 2009 22:14:01 -0700, Enrique wrote: This was extremely helpful. It took me awhile to get it working but now it works like a charm. Thanks Mr. Dibben (Excel MVP) "Gord Dibben" wrote: 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, just code to make a user's sheet visible. In the Thisworkbook Module.................... Private Sub Workbook_Open() Dim pword As String 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 Sheets("Dummy").Visible = False Case Is = "Pete": Sheets("Petesheet").Visible = True Sheets("Dummy").Visible = False End Select 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 Thu, 26 Mar 2009 14:21:01 -0700, Enrique wrote: I'm using Excel 2007 I have a team of sales individuals and have created a workbook which contains a worksheet for each individual. When they open it I only want them to be able to view their corresponding worksheet. I would like them to do the following: 1. Open workbook 2. Enter password to see their corresponding worksheet. 2. Enter data 4. Save workbook 5. Send back to me. |
Thread Tools | |
Display Modes | |
|
|