This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Amusing(?) Excel macros to send to your colleagues Creating the Rogue Excel Workbook (this blog)

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!

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

Posted by Andy Brown on 18 September 2024

In this blog

Creating a Macro-Enabled Workbook
Assigning macros to events for the workbook
Assigning Events to the Worksheet
Attaching code to the button
Saving and closing your workbook

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

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:

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:

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:

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:

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

Saving and closing your workbook

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
Amusing(?) Excel macros to send to your colleagues Creating the Rogue Excel Workbook (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Creating the Rogue Excel Workbook

Creating a Macro-Enabled Workbook

Assigning macros to events for the workbook

Assigning Events to the Worksheet

Attaching code to the button

Saving and closing your workbook

Head office

London

Manchester