560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Want to wind up your neighbour? Try this annoying Excel VBA code!
Part two of a two-part series of blogs
If you have a colleague who trusts you, you could always betray this trust by sending them a workbook which misbehaves: it won't close and you can't leave it!
Please note - this isn't a tutorial in Excel Visual Basic (although we do have one on this website, as well as a two-day course in Excel VBA). Instead, it just gives the instructions to follow to get the workbook to work.
First, create a new workbook, then save it as a new file:
Choose to save the file with a different name (the option shown is for Excel 2010, but this workbook will also work in all other versions of Excel).
Choose to save your workbook with an XLSM extension, so that it can contain macros:
Give your file an XLSM extension, as shown.
You can now create your workbook with the worksheets as shown. You can add the button using a textbox:
Choose this option to add a textbox (you may then need to format it to look more like a button).
You should now have something like this on your first sheet:
The sort of worksheet you should now have - note that the button won't do anything yet!
The next thing to do is to stop anyone closing or saving your workbook. To do this, first go into the VBA macro editor by pressing ALT + F11.
Now double-click on ThisWorkbook in what's called Project Explorer:
Double-click on this part of your workbook (if you can't see Project Explorer, press CTRL + R to display it).
Paste into the empty module (the space where you type macros) the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'prevent user closing down the file
Cancel = True
MsgBox "What's the matter - don't you like my figures?", vbExclamation, _
"Message from the dark side"
Private Sub Workbook_Deactivate()
'if user tries to leave this, just go back to it
'display another scary message
MsgBox "You can't escape that way ..."
This will stop anyone leaving or closing your workbook!
Time now to stop anyone seeing the other worksheet (the one with the figures on). To do this, we'll prevent anyone deactivating the first sheet. Double-click on its name in Project Explorer:
In VBA worksheets can have two names - the one in brackets is the one which appears in Excel.
Now paste in the following code:
Private Sub Worksheet_Deactivate()
'keep user on same worksheet
'and display sinister message
MsgBox "Don't you like this worksheet?", vbQuestion, "Scary ..."
Finally, we need to get the button to display helpful instructions. To do this, add a module to your code in the VBA code editor:
Right-click anywhere on the Excel workbook, and choose to insert a module.
Excel will create a new module for you, and take you to it:
The module will be called Module1 - you can now paste code in as shown below.
Paste into the empty code window the following:
Dim answer As Integer
answer = MsgBox("Have you turned your speaker on?", _
vbQuestion + vbYesNo, "Are we on air?")
If answer = vbYes Then
Application.Speech.Speak ("Welcome to the dark side ...")
MsgBox "Don't waste my time then ...", vbOKOnly, "Time-waster?"
The command above to "speak" doesn't work with all sound cards, so don't be too disappointed if you hear nothing!
You now need to attach this macro to your button. Return to Excel, and right-click on your button:
Right-click on the button you've created, and choose to assign a macro as shown.
You can now choose the macro you've written called ComfortUser, and select OK:
Choose the macro you want to assign to the button, then select OK.
Save your workbook, then try closing it. You should see this annoying message:
The message which should appear when you try to close the workbook.
You'll need to go into VBA and remove the code attached to ThisWorkbook before you can close the workbook! Remember not to save your changes at this point, otherwise it will be saved without your trick code in.
Your file is now ready to be sent to your unsuspecting victim! But remember - this blog just shows you what to do - the responsibility for doing it is all yours ...
|Parts of this blog|
25 Aytoun Street