Opening a workbook to a specific tab

Reidster78
NewLounger
Posts: 3
Joined: 25 Mar 2021, 08:58

Opening a workbook to a specific tab

Post by Reidster78 »

Hi (i just discovered this place recently and it's made its way to the top of my bookmark list!),

I am looking for some assistance with a VBA code. In my team we have a shared excel document, and everyone in the team has their own tab.

I am searching for a way for the sheet to open to the tab which is relevant to the person who opens the sheet.

I have found many tutorials showing how to open a workbook to a named tab - but not how to open based on the name of the person who opens it.

I hope this makes sense, i look forward to seeing how this can be done!

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Opening a workbook to a specific tab

Post by HansV »

Welcome to Eileen's Lounge!

What is the relationship between the person and the sheet tab? Are the sheet tabs named after the user's login name, or their full name, or an ID, or ...?
Best wishes,
Hans

Reidster78
NewLounger
Posts: 3
Joined: 25 Mar 2021, 08:58

Re: Opening a workbook to a specific tab

Post by Reidster78 »

Thanks!

The tabs are listed by the persons first name - so like - John, Mary, Chris etc

Our usernames are listed in excel like "john.smith"

The sheet previously worked like this, but i made some changes and it removed that function. Couldn't find any macros in the document anywhere either, which i though was weird

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Opening a workbook to a specific tab

Post by HansV »

In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left hand side.
Copy the following code into the module:

Code: Select all

Private Sub Workbook_Open()
    Dim strUser As String
    On Error Resume Next
    strUser = Split(Application.UserName, ".")(0)
    Worksheets(strUser).Select
    If Err Then
        MsgBox "There is no sheet named " & strUser, vbInformation
    End If
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm). If you save it as an ordinary workbook (.xlsx), the code will be removed.
Make sure that users allow macros when they open it.
Best wishes,
Hans

Reidster78
NewLounger
Posts: 3
Joined: 25 Mar 2021, 08:58

Re: Opening a workbook to a specific tab

Post by Reidster78 »

thanks for this, but it didn't work as expected.

When I ran in the marco editor, it told me my name in the msg box. But when i saved as .xlsm and reopened, it just opened to the tab i had saved under.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Opening a workbook to a specific tab

Post by HansV »

1) You wrote <<Our usernames are listed in excel like "john.smith">>
Do you mean the username as specified in File > Options > General, or the Windows (or Mac) login name, or ...?
The code curently uses the username from File > Options > General, assumes that it contains a period (as in mary.smith) and tries to activate the worksheet with the first name (mary in this example).

2) Are you sure that you stored the code in ThisWorkbook, and that macros aren't blocked?
Best wishes,
Hans