Hi, I’m here to torture you with more VBA! Run away! Run away! This is part two of the tales from the Programmer’s Purgatory. And once again, no it is not hell. When you are in Programmer’s Hell you spend all your time in meetings, and/or doing team building exercises *shudder*.
This time I’m unloading few random snippets of code that I produced during my sentence in the purgatory. For example, there is a vba macro that is supposed to extract some data from an excel file with ~20 worksheets. The users need to be able to add new worksheets to it, or move and modify existing ones (that is an absolute requirement, and arguing about it is pointless) so you can’t really protect the workbook. There are really two ways to access any given sheet:
- By it’s name: ThisDocument.Sheets(“Accounts Receivable 10″)
- By position: ThisDocument.Sheets(10)
There is of course an issue here. If you try to call worksheets by name, the users will promptly rename every single one. If you try to get it by position, they will rearrange the whole workbook. It is a lose-lose situation and almost every day our helpdesk has this very conversation:
We decided to be tricksy Hobbits and added the following snippet to every single sheet in the document that should not be renamed:
Sub Worksheet_Deactivate() Me.Name = "The Name of the Worksheet" End Sub
It’s funny because it is so subtle. They can rename the worksheet all they want, but as soon as they click on another sheet the name will just quietly revert to what it was supposed to be in the first place. No matter what they do to it, it won’t keep the new name. I imagine they will eventually give up, or call the Helpdesk and then we can tell them in our best BOFH voice “Read what it says in red on top of the worksheet. Do not rename!”
Of course techno-idioticus luseratis is the most ingenious animal on the planet. While they are unable to perform the simplest tasks in Windows or Office without assistance, they show almost limitless resourcefulness when it comes to circumventing the tricks we use to stop them from breaking things.
So if they can’t rename the worksheet they will delete and recreate it or do something equally silly. So we came up with an insidious way to make their life harder. Naturally, it couldn’t be easy because the fuckers at Microsoft decided that there is no reason to fire some sort of an event when a worksheet is deleted. I mean, who would ever want to capture such an insignificant event? No one, that is who.
So there is no real way of preventing the user from deleting a worksheet other than protecting it, or protecting the workbook but that naturally won’t work for two reasons. One reason is that they would often need to un-protect it in order to add/modify certain sheets. The second reason is that most of the time you can remove protection using tools like this one.
So we figured that we’ll just disable the Delete button on relevant Sheets. The user will still be able to get in trouble by moving the worksheet to another workbook, but at least the most obvious way to delete things will be disabled. This is a bit convoluted, but I blame Microsoft for not making a Worksheet_Delete method.
' Put this in it's own module or Workbook module Sub DeleteButton(ByVal enable As Boolean) Dim CommBarTmp, Commbar As CommandBar For Each Commbar In Application.CommandBars Set CommBarTmp = Commbar.FindControl(ID:=847, _ recursive:=True) If Not CommBarTmp Is Nothing Then _ CommBarTmp.Enabled = enable Next End Sub ' On each sheet put the following two subroutines: ' When browsing away, enable the Delete button Private Sub Worksheet_Deactivate() DeleteButton True End Sub ' When activated, disable the Delete button Private Sub Worksheet_Activate() DeleteButton False End Sub ' Add these two in the Workbook module: ' Enable the button before closing excel Private Sub Workbook_BeforeClose(Cancel As Boolean) DeleteButton True End Sub ' Enable the button when you switch to another workbook Private Sub Workbook_Deactivate() DeleteButton True End Sub
Note that you could use the Workbook_SheetActivate method to activate this behavior for all worksheets instead of using separate Worksheet_Activate and Worksheet_Deactivate methods. We didn’t do that because only some sheets need to be protected this way, while others are free game.
Finally, here is another frequent Helpdesk conversation that happens at least twice a day:
So here is yet another trick – we will forcefully save the document even if the user resists:
' Put all of these in ThisDocument module Sub AutoOpen() WaitAndSave End Sub ' Wait 5 minutes Sub WaitAndSave() Application.OnTime Now + TimeValue("00:5:00"), "Saver" End Sub ' Save the doc and wait again Sub Saver() ThisDocument.Save WaitAndSave End Sub
This way, the lusers can’t claim they lost 5 hours of work because Word sucks or because some macro crashed their Office or because there was a power outage. Now they can only lose up to 5 minutes of real work. I’m also considering combining this with the versioning feature of Word 2003 (and higher). This way if the user fucks something up they will be able to roll back their changes in 5 minute increments.
I hope you enjoyed this brief excursion into the terrifying world of madness which is VBA. Let’s hope there won’t be part 3, because even right now I feel that my sanity is slipping.