Open specific sheet when opening workbook trouble

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 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:

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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,

simply add a new sub as follows

Code:
Sub Auto_Open()
Sheets(“MENU”).Select
End Sub

I believe this should do the job. Don’t forget to test on a copy of the workbook first.


Edit: just read bottom of your code, is it not due to the fact that you have made the sheet very hidden?
Coops
 
Last edited:
Upvote 0
Try the following;

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

Worksheets(“menu”).visible = xlSheetVisible
worksheets(“menu”).Activate
End Sub
 
Last edited:
Upvote 0
Hi Coops,

sorry for the delay.

I tried the code but got an error, Compile error, Variable not defined

‘Worksheets(“menu”).Activate

Thanks for all your help and time, im not sure what the error means to be honest, im a 'learner' of VBA!! Thanks,
 
Upvote 0
Simply replace your last sub with the following.

Code:
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
        End If
    Next sh

Worksheets(“menu”).Visible = xlSheetVisible
worksheets(“menu”).Activate
End Sub
 
Last edited:
Upvote 0
Hi Coops,

That's what I did but then get the compile error. I have had a little read up and read that I hadn't defined it (DIM). No idea...something at the top?

Thanks,
Nick
 
Upvote 0
As long as you got it working, I removed the extra line of code in my last post for you rather than just making it a comment.

Coops
 
Upvote 0
The problem with the code in post #5 is the strange quotes around menu they should be " not “.
Code:
Private Sub Workbook_Open()
Dim sh As Long

    '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
        End If
    Next sh

    Worksheets("menu").Visible = xlSheetVisible
    Worksheets("menu").Activate

End Sub
 
Upvote 0
Hi,

That did it, the quotes. Thanks. However, its unearthed a different issue. If you understand the code originally posted, the file opens at the start to tab named 'Home'. This is in actual fact i the sheet i do need to open first. Not as I thought menu. The home tab is actually a log in page created in vba. Once the login credentials have been entered, its at that stage I need to open 'menu'.

Thank you all so far, hopefully you can help with where the code needs to be entered or if the code will still work?

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top