61

This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell (call it A1) to to show a certain name (call it Joe) no matter what someone types into it.

A1 should be blank until someone tries to type a name into it. When someone types a name - any name - it automatically changes to Joe after they hit enter.

Conditional formatting doesn't work as it appears it only addresses look of the value in cell A1.

A formula doesn't work because they would see something in the cell.

Auto-correct - while it would have the exact effect I'm looking for - doesn't work b/c that's stored on my computer and wouldn't transfer with the file.

Any ideas?

Adam
  • 601
  • 5
  • 4

2 Answers2

109

As a macro-less alternative, this won't change the value of the cell, but it will change the display of the cell. Right click on the cell in question, then click "Format Cells." In the number tab, click on Custom, then enter the following custom number format

"Joe";"Joe";"Joe";"Joe"

Explanation: Excel number formats have four parts, separated by semicolons. The value of the cell determines which part of the number format is used, and the format looks like this: 

[positive];[negative];[zero];[text]

Since each section is a hard-coded string, "Joe" will display no matter what is input, even if a formula is input (unless that formula returns an error). Usually number formats are used to display negative numbers in red, or align values at the decimal, or other cosmetic things. But they can also be used to mess with your friends.

Kyle
  • 2,406
  • 2
  • 11
  • 12
  • 12
    This is epic and hilarious. Now I just need to find a target... – Michael McGriff Sep 29 '15 at 17:47
  • 3
    This is absolutely sublime. Now I want to invent something just to have an excuse to do it to someone :) – Kaz Sep 29 '15 at 17:48
  • Why `"Joe";` four times? – JPhi1618 Sep 29 '15 at 18:41
  • 1
    Adding some information as to *why* this works as a format would really make this answer stellar. – Raystafarian Sep 29 '15 at 18:45
  • 22
    To really make someone think their Excel sheet is haunted, try [Ž̙̐́ͅa̴̺̯̝̱̜̦̣͂͆͟l̞̬̖̬͎̲̦͚ͭ̄̿̇ͨ̀̈̕͢g̶̼̺̲͓͉̫͍͕̭ͤ̈͑͝o͚̭̬̹ͮͨ̎̅̌͌ͤ̇ text](http://stackoverflow.com/questions/6579844/how-does-zalgo-text-work), for example `"J̼̼̝̟͇̺͖̹̄̋͌̽oe";"J̼̼̝̟͇̺͖̹̄̋͌̽oe";"J̼̼̝̟͇̺͖̹̄̋͌̽oe";"J̼̼̝̟͇̺͖̹̄̋͌̽oe"` (seems to work with one letter only) – user56reinstatemonica8 Sep 29 '15 at 18:57
  • @Raystafarian Good point, I wasn't expecting this to receive so much attention to be honest. – Kyle Sep 29 '15 at 19:26
  • 1
    I think under your gray box you mean "four" instead of "for". I can't suggest a one character edit. – Ross Millikan Sep 30 '15 at 05:05
  • This is very useful for *hiding* data in cells as well, as opposed to coloring the font white (which still shows when printed). To do that, just set the custom format to `;;;`. Also, rather than right-clicking and going into Format Cells, one can just type `Ctrl+1` to open that dialog for the current selection – airstrike Oct 12 '16 at 17:54
9

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
   Application.EnableEvents = False
      Range("A1").Value = "Joe"
   Application.EnableEvents = True
End Sub
Gary's Student
  • 19,266
  • 6
  • 25
  • 39