Category Archives: Excel, Word & VBA
Microsoft Excel, Word and VBA.
When I was young I loved playing with codes and ciphers. When I started to learn about Excel I thought it would be cool to use a spreadsheet with VBA to make it easier to break ciphers. So that’s what I set out to do. What I developed doesn’t break ciphers but it does make it easier to do it myself by replacing all occurrences of a letter when a substitute is chosen.
The spreadsheet is called Excel Cipher Assistant and you can download it in xls format. It works using Excels built-in macros so you will need to allow macros to run for it to work. I have included 10 encrypted messages to test your skill and you can easily add your own encrypted text to work on.
There are instructions within the spreadsheet.
How does it work?
The encrypted text is placed in the ‘Encoded Message’ cell. When the Decode button is pressed the Decode macro cycles through the characters in the Encoded Message cell and checks them against those entered in the ‘substitutions’ section. If a match is found it replaces the character with the one chosen by the user. When all characters have been checked the updated text is placed in the ‘Decoded Message’ cell and then it goes through the text and sets the style bold for each character that has been substituted.
Lately I have been thinking about ways to improve it, but I think I’ll just start again. I will add a post about it if I do. Feel free to modify it or change it.
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