In excel, I want the word "Name" to display but not print

Question

I have a census sheet in excel that has to list peoples name in a specified area. There are a set number of rooms that can contain people, but they are not all full at all times. I would like to have the name field display the word "Name" when there is no one in the room, but not print the word "Name". Then when that room fills up I would like it to be possible for anyone to type in the new persons name and have that name both display and print, completely replacing the word "Name", until that person leaves and the room is open again. Is there a way to have a cell display a place holder that only displays until someone types into that cell and then reappears when that cell becomes blank again?

Answer 1

Normally it is not possible. But Excel has a feature that you can "abuse" for this manner.

Create a column directly in front of the column you want to enter the data.

For example, if you have your empty cell with "Name" in C1, you insert a column before column C, so C1 becomes D1 and C1 is empty.

Now enter "Name" in C1, and make D1 empty.

Now comes the trick, change the width of column C to 1 pixels big. Because D1 is empty, C1 will continue to be displayed overlapping D1. But as soon as you type in text in D1, "Name" will be overlapped and no longer being shown. It might be visible by 1 pixel, but that is the best we can do.

Answer 2

This is an example for cell B9. Place the following Event macro in the worksheet code area. Once it is installed, it will continuously monitor the cell. If you ever clear the cell, the macro will put "Name" in the cell:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim MyCell As Range
   Set MyCell = Range("B9")
   If Intersect(MyCell, Target) Is Nothing Then Exit Sub
   If MyCell.Value <> "" Then Exit Sub
   Application.EnableEvents = False
      MyCell.Value = "Name"
   Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!