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.
I’ve always like secret stuff and have been interested in 1960’s spy technology. That may be why I like the idea of hiding sheets in an Excel workbook from other users. In Excel sheets can be hidden (xlsSheetsHidden) or very hidden (xlsSheetsVeryHidden). When you right click on the sheet label and select “Hide” the sheet is hidden. Sheets that have been hidden this way can be made visible by right clicking on one of the sheets labels and selecting “Unhide”. Sheets that are very hidden will not show up in the menu of available hidden sheets.
Very hidden sheets can only be made visible through the VBA editor.
Automating with VBA
At work I hide sheets in some spreadsheets that have sheets with data that I sometimes need to use but will be of no use to others and this should avoid anyone accidentally corrupting any of the data in those sheets. It may also be useful for a control panel sheet that I don’t want others playing with. So I wrote some simple code to do this.
I made a simple workbook to try this out for myself. There are two macros associated with workbook. The first is called HideSheets. It is used to toggle the visibility of Sheet2 and Sheet3 between visible and very hidden. The user must supply a password. The default password is set to “password”, but it can be easily changed in the VBA code.
The second macro called Auto_Close runs automatically when the user attempts to close the book. It hides the sheets set in that macro; in this case it’s set to hide the same sheets, Sheet2 and Sheet3. The user is then prompted to save the book. The purpose of this is to hide the sheets in case you forget to hide them before closing.
You can download a copy of the HidingSheetsUsingPassword spreadsheet in Office 2007 format. It contains the same code as below.
Sub HideSheets() ' This macro toggles worksheets listed between visible and very hidden ' and requires a password to run Dim myPassword As String myPassword = "password" ' Set password here Password = InputBox("Enter Password") If Password = "" Then Exit Sub ' Exit if null input or cancel ' Incorrect password If Password <> myPassword Then MsgBox Title:="Warning", prompt:="Incorrect Password" Exit Sub End If On Error Resume Next ' Correct password If Worksheets("Sheet2").Visible = True Then ' Check if one is visable. ' The sheet above is visiable so hide all required Worksheets("Sheet2").Visible = xlSheetVeryHidden Worksheets("Sheet3").Visible = xlSheetVeryHidden Else ' Sheets must be hidden, so unhide all required Worksheets("Sheet2").Visible = True Worksheets("Sheet3").Visible = True End If End Sub Private Sub Auto_Close() ' This macro hides sheets when the user closes the workbook ' It is a "Private Sub" to prevent it being listed with outher macros On Error Resume Next ' When workbook is closed hide sheets required Worksheets("Sheet2").Visible = xlSheetVeryHidden Worksheets("Sheet3").Visible = xlSheetVeryHidden End Sub
How secure is it?
Without password protecting the VBA Project it is not very secure as any user that knows their way around Excel will soon go into the VBA editor and unhide the sheet, but the sheets are probably safe from casual users. If the VBA project is password protected it will be more secure, but how secure will depend on the version of Excel you are using and even doing so does not encrypt the sheet so it may still be visible on some other spreadsheet software. I would not trust it with any really important data.
If you want to password protect the VBA project, then this is how you can do it
1. Go to the VBA editor
2. Right click on the VBAProject name in the Project box and select VBAProject Properties…
3. In the Properties dialog box select the Protection tab
4. Tick Lock project for viewing
Back in the ‘70s when I was 10 a friend and I had a secret club and we sent messages using ciphers. We stuck bits of paper with letters on them over the keys on our typewriters with sticky tape. Type in a message using the new letters on the keys and out came an enciphered message. Type an enciphered message and out came a deciphered one. Simple, but due to the different layout of the keys it messed up any typing skills I had.
Since then I have had an interest in cryptography, so when I started playing with VBA in Microsoft Word and Excel I naturally thought of what I could do with them and wrote some macros for Word to encypt and decrypt text using the Caesar ciher and Vigenère ciphers. These are two popular historic encryption methods.
The Caeser cipher
The Caesar cipher is named after Julius Caesar. It is believed that this is one of the ciphers he used to correspond with his army. With it all the letters in the alphabet are moved on a set number of places, in Caesar’s case three places. The recipient simply replaces all letters with those down the same number of places in the alphabet.
The Vigenère cipher
According to the Wikipedia Vigenère cipher article, the Vigenère cipher was originally invented by Giovan Battista Bellaso and was described in his book in 1553. I looked at this cipher when I was 10 but I did not understand it and moved on to others, but after reading about it on the internet I soon learned how to use it and it really is quite simple. It’s a great cipher to learn.
If someone got a copy of the ciphers I used they could easily decode any messages. The Vigenère cipher differs as it uses a keyword or phrase for enciphering and deciphering. Having a copy of the cipher does not help much… well not to most of us anyway. It is the key that you share with those the message is intended that is important.
The Vigenère cipher is a table of 26 Caesar ciphers.
How to use it
Traditionally all spaces are removed from the message. This makes the message harder to break. My Word macro ignores spaces and will leave them if they are included.
A keyword is chosen and written repeatedly above the message for the length of the message. Each letter of the plaintext is encoded by finding the intersection in the table between the plaintext letter and keyword letter. The first letter of the plaintext in the example below is M which is enciphered using the alphabet in row C, which is the first letter of the keyword. This is done by looking at the letter in row M and column C which is O. The rest of the message is enciphered the same way. Deciphering is done the same as enciphering.
Keyword: CATCATCATCATCATCATCATCATCATCATCAT Plaintext: MEETATTHEOLDMILLATTHEENDOFTHEROAD Ciphertext: OEXVAMVHXQLWOIENAMVHXGNWQFMJEKQAW
Another way to think about how the cipher works is to think of each character of the keyword corresponding to a number between 1 and 26. If we look at the keyword ‘CAT’ again and the number corresponding to each characters location in the alphabet we get 3, 1 and 19. These numbers represent how far we need to ‘shift’ each letter. In fact characters are shifted 1 place less than their spot in the alphabet bacuse there is no shift in the first column, so it would actually be 2, 0 and 18. This is the way that the Word macro works.
Once you understand how the cipher works it becomes obvious that the longer the keyword, the stronger the encryption as more ciphers are used in a more complex pattern. Also, the first column in the cipher corresponding to ‘A’ is not encoded at all, so if the single character ‘A’ is used as the keyword the message will not be encrypted.
Using Word to do the work
I made some macros for Microsoft Word that do the hard work of enciphering and deciphering. Simply select text in a document and push the appropriate button and the text will be updated. Download the word document to try it.
Download CaesarAndVigenereCiphers.docm (Word 2007 for PC only 75kb)
You are free to distribute or modify it or use the code for your own purposes. It could be easily improved. Rather than replacing the text in the document, the message could be displayed in a user form. The Caesar cipher could display all 26 deciphered versions of the text in a user form which would make it immediately obvious which one was used
I hope you have fun with it.