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!

  1. Amusing(?) Excel macros to send to your colleagues
  2. Creating the Rogue Excel Workbook (this blog)

Posted by Andy Brown on 14 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating the Rogue Excel Workbook

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.

Creating a Macro-Enabled Workbook

First, create a new workbook, then save it as a new file:

Choose file save as

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:

Choose macro enabled workbook

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:

Adding a textbox to Excel

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:

Help instructions

The sort of worksheet you should now have - note that the button won't do anything yet!

 

Assigning macros to events for the workbook

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:

Attach code to ThisWorkbook

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

 

'say why!

MsgBox "What's the matter - don't you like my figures?", vbExclamation, _

"Message from the dark side"

 

End Sub

Private Sub Workbook_Deactivate()

 

'if user tries to leave this, just go back to it

ThisWorkbook.Activate

 

'display another scary message

MsgBox "You can't escape that way ..."

 

End Sub

This will stop anyone leaving or closing your workbook!

Assigning Events to the Worksheet

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:

Double-click on the Sheet1

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

Sheet1.Select

 

'and display sinister message

MsgBox "Don't you like this worksheet?", vbQuestion, "Scary ..."

 

End Sub

Attaching code to the button

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:

Inserting a module

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:

Your new module

The module will be called Module1 - you can now paste code in as shown below.

 

Paste into the empty code window the following:

Sub ComfortUser()

 

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 ...")

Else

MsgBox "Don't waste my time then ...", vbOKOnly, "Time-waster?"

End If

 

End Sub

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 to assign macro

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:

Assigning macro

Choose the macro you want to assign to the button, then select OK.

 

Saving and closing your workbook

Save your workbook, then try closing it.  You should see this annoying message:

Preventing closure

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 ...

 

  1. Amusing(?) Excel macros to send to your colleagues
  2. Creating the Rogue Excel Workbook (this blog)
This blog has 0 threads Add post