Multiple Worksheets

[George]

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.

 

[Luke M]

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.

 

[Eduardo]

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

 

[BSc Chem Eng Rick]

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

 

[George]

Thanks so much that is exactly what I wanted to do

 

[Luke M]

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.

 

[Sheri]

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!