Cell to retain a default value when it is cleared/deleted

Hi,

I am currently working on a excel sheet which will be used by a multiple users. My problem is
I want to have a cell say A1, which has a text say "Enter Name". When a user tries to enter his name the cell will chage to the name he enters. Let us assume that the user entered his name "Mani". Now A1 has the value "Mani".
If I delete/clear the value of A1, I want it to retain the value "Enter Name" automatically. Can this be possible?

thanks in advance.

Yes, but it will require the use of VBA. That means everyone who uses the workbook will have to enable macros. Are there any other macros already in this workbook? If not, then this would be a notable change. If so, then it's not big deal.

Here's a macro that would do it:

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim cell As Range

    For Each cell In Target
        If cell.Address = "$A$1" And cell = "" Then cell = "Enter name here"
    Next cell

End Sub

How to use the macro:

1. Open up your workbook
2. Right-click on the sheet tab
3. Select VIEW CODE from the popup context menu
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.
========

Now make changes to cell A1 and watch what happens.

You will have to save the workbook as a macro-enabled (*.xlsm) file.

JB,

Why not narrow down the $A$1 with intersect(Target,Range("A1"))

Maybe someone has selected the complete column and uses CTRL+Enter to fill it.

Originally Posted by rwgrietveld

JB,

Why not narrow down the $A$1 with intersect(Target,Range("A1"))

Maybe someone has selected the complete column and uses CTRL+Enter to fill it.

Personal preference. I use the intersect method, too. It's not any better, it's just different.

My suggestion will accurately evaluate cell A1 regardless of how many cells were selected and edited at the same time. If they delete the entire column, A1 will fill itself back in.

Yes, there are about 5 different ways to do this, I offered one. Sometimes one is enough.

Errmmm.... maybe reserve one cell for the label "Enter name" and enter the actual data in the adjacent cell. Just like any old form....

My point is the fact that your code checks ALL cells in the Target range. If an entire row/column is selected this might take a while.