Practical Joke

[Jeff Podgorski]

Hello,

I would like to play a joke on a collegue, is there anyway to place a formula in a cell that displays text, something like "Hi Mike" anytime data is entered into that cell?

Thanks,

 

[Jonmo1]

Try looking and posting in this thread

http://www.mrexcel.com/forum/showthread.php?t=27058&page=38

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = "Hello Mike"
Application.EnableEvents = True
End Sub

 

[Jeff Podgorski]

Thank you, that's great!

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = "Hello Mike"
Application.EnableEvents = True
End Sub

 

[Brian from Maui]

How can you modify the code so you can delete the remark?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Value <> "" Then Target.Value = "Hello Mike"
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

Suggest changing to

If Target.Cells(1, 1).Value <> "" Then Target.Cells(1, 1).Value = "Hello Mike"

otherwise if multiple cells are changed at once (eg Multiple cell Delete or Copy/Paste, Ctrl+Enter entries) the code will error with two likely consequences

1. They will see the code and likely twig to what is happening, and/or

2. Unless the user knows what they are doing in the VB editor (which they probably don't if you are playing this prank on them) they will end up with events disabled which may undesirably impact other code.

 

[RoryA]

Give all cells a custom number format of:
"Hi Mike";"Hi Mike";"Hi Mike";"Hi Mike"
that way you wont disturb any actual data.

 

[MrKowz]

Give all cells a custom number format of:
"Hi Mike";"Hi Mike";"Hi Mike";"Hi Mike"
that way you wont disturb any actual data.

THAT one is going in my book of useless (but fun) knowledge.

 

[TinaP]

Give all cells a custom number format of:
"Hi Mike";"Hi Mike";"Hi Mike";"Hi Mike"
that way you wont disturb any actual data.

That's just evil. I like it.