Excel: Hiding sheets based on username
This is a follow up to last week’s post Hiding sheets using VBA and a password. I have been thinking about other ways to hide sheets in Excel from other users.
At work we share space on a server and I have a number of spreadsheets on it that other people have access to. These spreadsheets only really apply to my work, but from time to time other people view them to find out some information. There is nothing really secret in them, but I would not like someone changing some of the data in some of the sheets – the information in those sheets is not going to be useful to others, in fact it will only be confusing. So I have been thinking about other methods to try and hide these sheets that is even easier than having to enter a password.
My plan this week is to add in two macros to some of these spreadsheets. One will be a Auto_Open sub and the other is an Auto_Close one. Macros with these names automatically run when a spreadsheet is opened or closed. The open one will unhide some sheets if the operator’s username matches the one set in the macro (mine) and the close macro one will ensure the sheets are hideden again.
I have found two ways to identify usernames from within Excel. The first is using Application.UserName which returns the username of the current Microsoft Office user. The other way is to use Environ(“USERNAME”) which returns the current Windows username. I’m just going to use the first one as it should be sufficient for my needs. As you can imagine this will not be all that secure as anyone could either; open the file on my computer, change the office username to my username, manually unhide the sheets from within the VBA or remove the user check from the VBA code as I don’t plan on password protecting the project.
I have made a simple spreadsheet HidingSheetsUsingUsername in Office 2007 format to demonstrate this. It contains the code below.
This piece of code will return both usernames and display them in a pop up:
Sub GetUsernames() OfficeUserName = Application.UserName ' Excel username WinUser = Environ("USERNAME") ' Windows username MsgBox "Application Username: " & OfficeUserName & Chr$(10) _ & "Windows Username: " & WinUser End Sub
These are the macros I will use to automatically unhide and hide the sheets:
Private Sub Auto_Open() ' This macro unhides sheets when the user set below opens the workbook ' It is a Private Sub to prevent it being listed with other macros On Error Resume Next If Application.UserName = "Enter Username Here" Then Worksheets("Sheet2").Visible = True ' First sheet to be made visible Worksheets("Sheet3").Visible = True ' More can be listed here End If End Sub Private Sub Auto_Close() ' This macro ensures the sheets are hidden when any user closes the workbook On Error Resume Next Worksheets("Sheet2").Visible = xlSheetVeryHidden ' Hide worksheets Worksheets("Sheet3").Visible = xlSheetVeryHidden ' Other sheets to be hidden End Sub
There is a great page at Office Zealot Excel VBA: Function to get logged in user name (plus the Environ Function) that has more information about getting system data using the Environ function. It was published in 2004, but it still seems current on my Office 2007, Windows 7 system.