To achieve exactly what you asked you could put this code in the 'ThisWorkbook' module of the Code section:
Code:
Private Sub Workbook_Open()
ShowSheet
End Sub
Sub ShowSheet()
Dim ws As Worksheet
Dim wsAllowed As Worksheet
'If SuperUser, show all:
If Application.UserName = "SuperUserName" Then 'insert Super User's name here
For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next
Exit Sub
End If
'If not a super user, only show the associated sheet
Set wsAllowed = GetAllowedSheet
wsAllowed.Visible = xlSheetVisible 'make sure the sheet is visible before hiding the others
'Hide all sheets that user isn't permitted to see.
For Each ws In Worksheets
If ws.Name <> wsAllowed.Name Then ws.Visible = xlSheetHidden
Next
End Sub
Function GetAllowedSheet() As Worksheet
'Set the sheet each user is allowed to use here.
'adapt the case statements for correct users and correct sheets to display
Select Case Application.UserName
Case "User 1"
Set GetAllowedSheet = Sheets("Sheet2")
Case "User 2"
Set GetAllowedSheet = Sheets("Sheet1")
Case Else
'If code gets here then User Name is unhandled.
End Select
End Function
Naturally it would need modifying to have the correct usernames and to handle what would happen if the username is unknown.
Having said that I'd keep all sheets visible but in the 'WorkSheet_Change' event of each sheet I'd check the username and if they aren't allowed to make any, pop up a message box and undo the change.
