security for tabs

[pothios]

hi,

i have an excel sheet with multiple tabs. is there a way to restrict access
of each different tabs?

 

[Gary''s Student]

If you want to protect data/formulas on a tab, then use protection.
If you want to restrict access to a tab, then hide the tab.

 

[Jacob Skaria]

Do you mean

From menu Format>Sheet>Hide

--which will hide the selected tab

If this post helps click Yes

 

[pothios]

but other users will still be able to unhide the sheet. is there something
like password protect for each tab and u cant access the tab unless u key in
a password?

 

[Gord Dibben]

Not too easily.

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

 

[Dave Peterson]

Any suggestion I would give would depend on workbook protection (and probably
macros).

And both of these are easily bypassed.

If you want to prevent others from seeing that data on those other sheets, then
don't use excel. It's not made for this kind of thing.

Or create multiple workbooks (single worksheets only) and share them.