Trying to make sure I post corectly from Title to end....
Working in Excel 2007 and I've done a lot of reasearch on this before posting to hopefully avoid getting flamed for either a repeat post or stupid question - not sure I'm succesful on either count...
What I intend:
- Workbook has multiple Sheets
- All Sheets are veryhidden on Open
- On Open check for username to determine which Sheets are unhidden (some users should have only one Sheet visible, other users will have multiple, a few will have all visible)
- Open Sheets
- Allow edits by username
- On Save/Close veryhide all Sheets
Here's the code I have in ThisWorkbook:
Code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sh As Worksheet
For Each Sh In Sheets
Sh.Visible = True
Next Sh
End Sub
Private Sub Workbook_Open()
Dim Sh As Worksheet
Select Case Environ("username")
' following user has access to only one Sheet
Case Is = "userA"
Sheets("UserA").Visible = True
Sheets("UserA").Select
' following user has access to three Sheets
Case Is = "userB"
[do not know how to insert code to unhide three specific Sheets]
' following user has access to all Sheets
Case Is = "userC"
[do not know how to insert code to unhide all Sheets]
End Select
For Each Sh In Sheets
If Sh.Name <> ActiveSheet.Name Then
Sh.Visible = xlSheetVeryHidden
End If
Next Sh
End Sub
Display More
I've found multiple posts both here and in other forums about arrays and other methods to specify multiple Sheets to unhide but insertion of all return an error of some sort. No doubt because despite a lot of trial and error I really have no idea how to include the additional code in what I've aready done.
Thanks in advance for any help!