Multiple Worksheets
in [General]
|
From: George on 29 Apr 2010 10:34 This is what I want to do I have a workbook with multiple sheets in it 15 to 20 with a master sheet to add up all the data The workbook needs to be viewed by several people but I only want each person seeing the data for their page. Is there a way to blank out the worksheet then be able to see it if you have the password. I do realize that you can just have multiple workbooks with the workbooks linked but they are in buildings around the country and you can't link workbooks with office live workspace.
From: Luke M on 29 Apr 2010 10:52 Non-secure method: You trust each person to view only their sheet, or use filters on the summary sheet to display their data: Secure method: There is none! If you let someone open (or link to) a workbook, they will be able to access any and all information contained therein. If you really need to keep people from viewing each other's data, you're going to need to physically seperate the data. -- Best Regards, Luke M "George" <George(a)discussions.microsoft.com> wrote in message news:19C1CFD6-5D19-438C-93EE-870050F13B4B(a)microsoft.com... > This is what I want to do I have a workbook with multiple sheets in it 15 > to > 20 with a master sheet to add up all the data The workbook needs to be > viewed > by several people but I only want each person seeing the data for their > page. > Is there a way to blank out the worksheet then be able to see it if you > have > the password. I do realize that you can just have multiple workbooks with > the workbooks linked but they are in buildings around the country and you > can't link workbooks with office live workspace.
From: Eduardo on 29 Apr 2010 10:58 Hi, what you can do is to hide the sheets and then in order to unhide them each user will have to enter a password, you will have to copy the code below in each worksheet before hidding it, and will have to change the password for each one. To copy the password, right click on the mouse in the tab name, view code and enter this code. In the example below the password is Manager and is located in three places, ensure to change it for yours private Sub Worksheet_Activate() Dim strPassword As String On Error Resume Next Me.Protect Password:="MANAGER" Me.Columns.Hidden = True strPassword = InputBox("Enter password to access DATA sheet") If strPassword = "" Then ActiveSheet.Visible = False Worksheets("Menu").Select Exit Sub ElseIf strPassword <> "MANAGER" Then MsgBox "Password Incorrect " ActiveSheet.Visible = False Worksheets("Menu").Select Exit Sub Else Me.Unprotect Password:="MANAGER" Me.Columns.Hidden = False End If Range("a1").Select On Error GoTo 0 End Sub Private Sub Worksheet_Deactivate() On Error Resume Next Me.Columns.Hidden = True On Error GoTo 0 End Sub "George" wrote: > This is what I want to do I have a workbook with multiple sheets in it 15 to > 20 with a master sheet to add up all the data The workbook needs to be viewed > by several people but I only want each person seeing the data for their page. > Is there a way to blank out the worksheet then be able to see it if you have > the password. I do realize that you can just have multiple workbooks with > the workbooks linked but they are in buildings around the country and you > can't link workbooks with office live workspace.
From: BSc Chem Eng Rick on 29 Apr 2010 11:01 George, You can do this with some VB code in the WORKBOOK OBJECT. Here is a sample, when the workbook is opened only Sheet1 is visible until a name that matches a defined case is entered. Then that specific worksheet is made visible. When you close the workbook, all the sheets are again hidden expect for Sheet1. Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim i As Integer Worksheets(1).Visible = -1 For i = 2 To Worksheets.Count Worksheets(i).Visible = 2 Next i End Sub Private Sub Workbook_Open() Dim Name As String Name = Application.InputBox("Please Enter Password", Type:=2) Select Case Name Case "John" Worksheets(2).Visible = -1 Worksheets(1).Visible = 2 Case "Susan" Worksheets(3).Visible = -1 Worksheets(1).Visible = 2 End Select End Sub -- If this helps, please click "Yes" <><><><><><><><><><><> "George" wrote: > This is what I want to do I have a workbook with multiple sheets in it 15 to > 20 with a master sheet to add up all the data The workbook needs to be viewed > by several people but I only want each person seeing the data for their page. > Is there a way to blank out the worksheet then be able to see it if you have > the password. I do realize that you can just have multiple workbooks with > the workbooks linked but they are in buildings around the country and you > can't link workbooks with office live workspace.
From: George on 29 Apr 2010 11:28
Thanks so much that is exactly what I wanted to do "Eduardo" wrote: > Hi, > > what you can do is to hide the sheets and then in order to unhide them each > user will have to enter a password, you will have to copy the code below in > each worksheet before hidding it, and will have to change the password for > each one. To copy the password, right click on the mouse in the tab name, > view code and enter this code. In the example below the password is Manager > and is located in three places, ensure to change it for yours > > private Sub Worksheet_Activate() > Dim strPassword As String > On Error Resume Next > Me.Protect Password:="MANAGER" > Me.Columns.Hidden = True > > strPassword = InputBox("Enter password to access DATA sheet") > > If strPassword = "" Then > ActiveSheet.Visible = False > Worksheets("Menu").Select > Exit Sub > ElseIf strPassword <> "MANAGER" Then > MsgBox "Password Incorrect " > ActiveSheet.Visible = False > Worksheets("Menu").Select > Exit Sub > Else > Me.Unprotect Password:="MANAGER" > Me.Columns.Hidden = False > End If > Range("a1").Select > > On Error GoTo 0 > End Sub > > Private Sub Worksheet_Deactivate() > On Error Resume Next > Me.Columns.Hidden = True > On Error GoTo 0 > End Sub > > > "George" wrote: > > > This is what I want to do I have a workbook with multiple sheets in it 15 to > > 20 with a master sheet to add up all the data The workbook needs to be viewed > > by several people but I only want each person seeing the data for their page. > > Is there a way to blank out the worksheet then be able to see it if you have > > the password. I do realize that you can just have multiple workbooks with > > the workbooks linked but they are in buildings around the country and you > > can't link workbooks with office live workspace. |