Show only one sheet in a workbook

[SeventyOne]

I have multiple sheets within a workbook, but I only want one to be shown based on a user ID/password that the user enters. I have heard that creating a user form and then a case statement is the easiest way to do this, but am having a difficult time setting it up. Any help would be appreciated. Thanks.

 

[lenze]

Hi and welcome to the board!!!
You can do this based on the log-in name of each user. You will need, however, a dummy sheet that is always visible (Say Sheet1). Now, in the ThisWorkBook module (RightClick the Excel Icon to the left of the word "File" on the menu bar and choose "View Code")

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Sheets
  If ws.Name <> "Sheet1" Then ws.Visible = xlSheetHidden
  Next ws
End Sub
 
Private Sub Workbook_Open()
Select Case Environ("UserName")
Case "User1": Worksheets("User1").Visible = xlSheetVisible
Case "User2": Worksheets("User2").Visible = xlSheetVisible
"etc
Case Else:
End Select
End Sub

Change UserNames and Sheet Names as needed.

HTH
lenze

 

[SeventyOne]

Thanks! Another question is when I go to save the file without closing, all sheets are hidden from view, even though my user name allows me to view all sheets. Is there a way to prevent this from happening?

 

[lenze]

Maybe

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
If Environ("UserName") <> "YourName" Then
   For Each ws In Sheets
      If ws.Name <> "Sheet1" Then ws.Visible = xlSheetHidden
      Next ws
End If
End Sub

lenze

 

[lenze]

This might be better!!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
If Environ("UserName") = "YourName" Then
   rspn = MsgBox ("Hide Sheets and Close?",vbYesNo, "Save")
   If rspn = vbNo Then Exit Sub 
End If
For Each ws In Sheets
If ws.Name <> "Sheet1" Then ws.Visible = xlSheetHidden
Next ws
End If
End Sub

Have to think about it! Trying to avoid pitfalls!
You also might want to change each instance of

ws.Visible = xlSheetHidden

to

ws.Visible = xlSheetVeryHidden

This will make the sheets available only via code, so the user can't bypass the macros to view forbidden sheets.

lenze