You may have created an Excel workbook that has several end users, and you want certain users to be able to view selected tabs.  Alternatively, you might want to provide a unique view to some users, perhaps a dashboard-feel (no visible Excel tabs and automatic full-screen mode) to senior management users, and the regular Excel view to other users.  This post will show you how to set up an Excel workbook that personalizes access to tabs or work space view based on the user’s computer name.

For instance, let’s say you have an Excel workbook with five worksheet tabs, and you are sending it to two users, with the following specific user requirements:

• User 1 (Sala, Baban) – Functional Team Member; needs to see final output and supporting analysis on worksheets 2-3, but does not need to see back-end data on sheets 4-5
• User 2 (Akpan,Fred) – Team Executive; needs to see final output on worksheet 1 in a dashboard-optimized view (full screen view with no visible worksheet tabs); does not need to see tabs 2-5

Place the following code in the ThisWorkbook sheet code area  (This can be accessed by clicking Developer Tab >> VBA Editor).

Private Sub Workbook_Open()

If Application.UserName = “Sala, Baban” Then

Sheets(“Sheet 4”).Visible = False
Sheets(“Sheet 5”).Visible = False

ElseIf Application.UserName = “Akpan, Fred” Then

Application.DisplayFullScreen = True

ActiveWindow.DisplayWorkbookTabs = False

Sheets(“Sheet 2”).Visible = False
Sheets(“Sheet 3”).Visible = False
Sheets(“Sheet 4”).Visible = False
Sheets(“Sheet 5”).Visible = False

Else

Exit Sub

End If

End Sub

A couple of notes on the above code:

This code assumes the Excel workbook will only be distributed to three users. So, other than Akpan, Fred and Sala, Baba, users that open up the Excel workbook will have full access to the five worksheets.  If the intent is to have only the originating user have access to all tabs, additional lines of code can be included to restrict access in varying degrees. For instance, the below code will restrict all other users apart from the originating user (e.g. “Doe, Jane”) to only the first tab:

Private Sub Workbook_Open()

If Application.UserName = “Sala, Baban” Then

Sheets(“Sheet 4”).Visible = False
Sheets(“Sheet 5”).Visible = False

ElseIf Application.UserName = “Akpan, Fred” Then

Application.DisplayFullScreen = True

ActiveWindow.DisplayWorkbookTabs = False

Sheets(“Sheet 2”).Visible = False
Sheets(“Sheet 3”).Visible = False
Sheets(“Sheet 4”).Visible = False
Sheets(“Sheet 5”).Visible = False

ElseIf Application.UserName <> “Doe, Jane” Then

Sheets(“Sheet 2”).Visible = False
Sheets(“Sheet 3”).Visible = False
Sheets(“Sheet 4”).Visible = False
Sheets(“Sheet 5”).Visible = False

End If

End Sub

The sheet names (“Sheet 2”, “Sheet 3”, etc.) must be updated to match the actual worksheet names in your Excel workbook. Also, be sure you have the Excel user name spelt correctly, accounting for placements of abbreviated middle names and punctuation in the code.