# Gentle VBA prank



## DarthWilbur (Feb 23, 2012)

Hey all,

I'm looking at putting a VBA prank in a colleague's xlsm.

Basically I want a macro that monitors for any changes in the text in cells B:B. (This could be blank to not blank, or not blank to different not blank)

If any changes are made it will delete the new text and replace it with a custom message. (The custom message can be the same every time).

Does anyone know how to do this?

Cheers,
Darth Wilbur


----------



## hiker95 (Feb 23, 2012)

DarthWilbur,

See:
Good Excel Practical Jokes, Pranks, Mean Tricks, etc. 
http://www.mrexcel.com/forum/showthread.php?t=27058


----------



## DarthWilbur (Feb 23, 2012)

Thanks, Hiker.
But I did see that thread earlier. It doesn't help with what I'd like to achieve.


----------



## SteveO59L (Feb 24, 2012)

Not that I would condone such practices, but possibly as a worksheet_change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Value = "Stop Changing These Cells"
End If

End Sub


----------



## Domski (Feb 24, 2012)

Such an important application should really turn events off to stop the code triggering itself and include some error handling 


```
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrorHandler
    Application.EnableEvents = False

    If Target.Column = 2 Then
        Target.Value = "Stop Changing These Cells"
    End If

CleanExit:
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    MsgBox Err & " - " & Err.Description
    GoTo CleanExit

End Sub
```

Dom


----------



## Colin Legg (Feb 24, 2012)

That should be Resume CleanExit rather than GoTo CleanExit, yes yes?


----------



## DarthWilbur (Feb 26, 2012)

Thank you,SteveO59L, Domski and Colin Legg!

Works perfectly! 
Looking forward to getting a confused query from target.coworker today.


----------



## T. Valko (Feb 27, 2012)

DarthWilbur said:


> Thank you,SteveO59L, Domski and Colin Legg!
> 
> Works perfectly!
> Looking forward to getting a confused query from target.coworker today.


target.coworker


----------

