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:
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
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!