Gentle VBA prank

[DarthWilbur]

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]

DarthWilbur,

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

 

[DarthWilbur]

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

 

[SteveO59L]

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]

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]

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

 

[DarthWilbur]

Thank you,SteveO59L, Domski and Colin Legg!

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

 

[T. Valko]

Thank you,SteveO59L, Domski and Colin Legg!

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

target.coworker