Multiple Worksheets
in [General]
|
From: Luke M on 29 Apr 2010 13:32 Please, please note that Eduardo's code doesn't provide security. Anyone with a little bit of knowledge of VB will be able to override this code, and get to the hidden data. -- Best Regards, Luke M "George" <George(a)discussions.microsoft.com> wrote in message news:1A917CCA-68CA-493E-A55B-80AF4BEF40C8(a)microsoft.com... > 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.
From: Sheri on 13 May 2010 22:25
I have a similar situation as George, but I want the user to be able to open multiple worksheets and have all the rest of the worksheets remain hidden. The code I currently have only has worksheet1 visible and prompts for a username and password. If they enter the correct username/password, it will unhide one worksheet, but not multiple sheets. If the enter an incorrect username/password, it gives them an error stating "you do have the authorization to view this worksheet." Any suggestions? Thank you! "BSc Chem Eng Rick" wrote: > 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. |