tab password
 

How can I create different passwords for 1 worksheet with differnt tabs to
prevent individuals from seeing other tabs?

tab password
 

Hi Keni2020,

Try something like:

'=============
Option Explicit

Private Const sStr As String = "COMMON" '<<=== CHANGE

'-------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim SH As Object

For Each SH In Me.Sheets
With SH
If UCase(.Name) < sStr Then
SH.Visible = xlSheetVeryHidden
End If
End With
Next SH
End Sub

'-------------

Private Sub Workbook_Open()
Dim SH As Object
Dim arr As Variant
Dim res As String

res = InputBox("Please enter password")

Select Case res
Case "MICKEY": arr = Array("Sheet1", "Sheet3") '<<=== CHANGE
Case "MINNIE": arr = Array("Sheet1", "Sheet2") '<<=== CHANGE
Case "MOUSE": arr = Array("Sheet2") '<<=== CHANGE
Case Else: Exit Sub
End Select

For Each SH In Me.Sheets
With SH
If IsError(Application.Match(.Name, arr, 0)) Then
If UCase(.Name) < sStr Then
SH.Visible = xlSheetVeryHidden
End If
Else
.Visible = xlSheetVisible
End If
End With
Next SH

End Sub
'<<=============

Change "COMMON" to the name of a sheet (perhaps an empty dummy sheet) which
should be available to all users - at least one sheet must be visible in any
workbook.

Change MICKEY, MINNIE and MOUSE to your required passwords.

Change the sheet arrays to reflect the sheets which should be available to
the respective user.

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"keni2020" wrote in message
...
How can I create different passwords for 1 worksheet with differnt tabs to
prevent individuals from seeing other tabs?