nparsons75
Well-known Member
- Joined
- Sep 23, 2013
- Messages
- 1,254
- Office Version
- 2016
Hi, I have a spreadsheet containing multiple worksheets. When a user opens the file, I need a specific worksheet to open. I have the following code currently in this workbook. I am hoping someone can help me with what code I would need to add and where in order to open the sheet I need to open. The sheet is named 'MENU'. in properties, sheet17 (MENU). I really hope someone can help. Thank you in advance.
The code is:
The code is:
Code:
Option ExplicitDim shtCurrent As Worksheet
Const shtHome As String = "Home"
Const shtUsers As String = "Users"
Const shtEnableMacros As String = "EnableMacros"
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim wsUsers As Worksheet
Dim sh As Integer, c As Integer
Dim ws As String
If IsError(Worksheets("Users").Range("B1")) = True Or Worksheets("Users").Range("B1") < 2 Then
Worksheets(shtHome).Visible = True
Worksheets(shtEnableMacros).Visible = xlSheetHidden
Exit Sub 'added 19-Jul-2016 m can be error value if invalid username has been entered
End If
Set wsUsers = Worksheets(shtUsers)
If Worksheets("Users").Range("B1") < 2 Then Exit Sub
If CBool(wsUsers.Cells(Worksheets("Users").Range("B1"), 3).Value) = True Then
'admin user
For sh = 1 To Worksheets.Count
Worksheets(sh).Visible = xlSheetVisible
Next sh
Else
'show users sheet(s)
c = 4
On Error Resume Next
Do
ws = CStr(wsUsers.Cells(Worksheets("Users").Range("B1"), c).Text)
If Len(ws) = 0 Then Exit Do
Worksheets(ws).Visible = xlSheetVisible
c = c + 1
Loop
End If
Worksheets(shtHome).Visible = xlSheetHidden
Worksheets(shtEnableMacros).Visible = xlSheetHidden
shtCurrent.Activate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Integer
Set shtCurrent = ActiveSheet
Worksheets(shtEnableMacros).Visible = xlSheetVisible
For sh = 1 To Worksheets.Count
If Worksheets(sh).Name <> shtEnableMacros Then
Worksheets(sh).Visible = xlSheetVeryHidden
End If
Next sh
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Workbook_BeforeClose False
End Sub
Private Sub Workbook_Open()
Dim sh As Integer, c As Integer
Dim rng As Range
Dim UsersName As String, pw As String, ws As String
Dim wsUsers As Worksheet
'hide all but sheet named "Home"
Worksheets(shtHome).Visible = xlSheetVisible
Worksheets(shtHome).UserName.Text = ""
Worksheets(shtHome).Password.Text = ""
Worksheets("Users").Range("B1") = 0
For sh = 1 To Worksheets.Count
If Worksheets(sh).Name <> shtHome Then
Worksheets(sh).Visible = xlSheetVeryHidden
Worksheets("menu").Activate
End If
Next sh
End Sub