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,436
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,636