Text which disappears when new text is entered but reappears if the new text is deleted

[Detectiveclem]

Help needed I would like to have greyed-out text (watermark?) in some cells to give users short instructions for that particular cell. If they type something in that cell, the text they input is in plain dark black...but if they delete what they type, the cell goes back to having the greyed-out text.

So just for an example, one cell might say ENTER LAST NAME. The next cell going down the same column may say ENTER YOUR DATE OF BIRTH. When the user starts typing into these cells the greyed-out text disappears and their inputted text shows up in DARK BLACK. BUT if they delete what they typed, the original grey-out text reappears (e.g. ENTER YOUR LAST NAME, or what ever was in the cell previously reappears greyed-out)

As this spreadsheet will be shared with others, I am looking for a solution which avoids VBA.

Does anyone have a suggestion?

Thank you.

 

[Anthony47]

Maybe in Office 2025...
In the meantime you could use Data validation:
-select the starting cell and start the data validation wizard
--in the Setting tab, select Allow=Any value
--in the Input Message tab, type "Enter Last Name"
--click Ok to close data validation

Now if you select that cell, the Input Message will show up; it will disappear when you select a different cell

Add more data validations to the other cells in the row; then copy these row and Paste-special data validation on the rows below

Of course you may take advantage of the data validation fetures, for example to allow only dates in cells that need a date

Bye

 

[Detectiveclem]

Thank you Anthony47.

This partially could be used as a work around, but ideally I would prefer even a greyed-out watermark in the relevant cells which will disappear when text is entered being replaced with text in black irrespective of if is subsequently deleted.

I am sure this must be possible, but I just don't know how to achieve it.

Any thoughts?

 

[Taul]

You could try a Custom Format

[Black]000000;;[Color15]"(First Name)";@

It is not exactly as you requested but it will give the grey name look that you wanted and when you type in the cell the text will turn black.
To delete the text, just enter 0 (zero), that triggers the format an will leave you with an empty looking cell with the watermark.

Book2
	A	B	C
1
2		(order number)
3
4		(First Name)
5
6		(Last Name)
7
8
Sheet1

 

[Taul]

Another way is to cheat.
Put the watermark text in the next cell and reduce the column width to say 0.1 wide, indent the text to the right so it looks like it is in the cell to the left. You can also play with the number of spaces after the watermark text.

Custom Format watermark.xlsx
	A	B	C	D	E	F	G
1
2			Watermark			First Name
3
4		Watermark in cell C2			First Name in cell F4
5		with spaces after the word			Column F width = 0.1
6
7
Sheet2

 

[Detectiveclem]

Hi Taul, thank you. I am not sure how to add the code you provided, can you please lead me through the process. I am not sure about the watermark you mention and would prefer code as you suggested.

Any text in column C I want to remain the same, but in column D I want the person using the spreadsheet to for example type their last name in cell D3 which will replace the Greyed instructional text with their surname in black.

Any help to achieve this would be greatly appreciated.

Thank you.

 

[Rick Rothstein]

VBA code will not run while a cell is in Edit Mode, so if your default text is gray in color, the new text the user types in the cell will be gray until the value typed into the cell is committed at which point it will turn black. Assuming you start a new sheet with the default text already in place (as your example shows), then give this event code a try (I only set it up for the two entries for cells D3 and D4... you can add the rest)...

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Count = 1 Then
    Dim Colr As Long, Txt As String
    If Target.Address(0, 0) = "D3" Then
      Txt = "Enter Last Name Here"
    ElseIf Target.Address(0, 0) = "D4" Then
      Txt = "Enter First Name Here"
    End If
    
    Application.EnableEvents = False
    If Len(Target.Value) = 0 Or Target.Value = Txt Then
      Target.Font.ColorIndex = 16
      Target.Value = Txt
    Else
      Target.Font.ColorIndex = 1
    End If
    Application.EnableEvents = True
  End If
  
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Taul]

Hi,
I assume you mean the code from post #4

Select the cell where you want the placeholder text (cell D3)
On the ribbon, go to FORMAT
Select FORMAT CELLS
In the CATEGORY list, select CUSTOM
Just under the TYPE, paste the code given in post#4
Click OK

Also, have a go at using Rick’s code, he has listed the instructions. It provides exactly what you asked for in the original request.

Cheers
Paul

 

[Peter_SSs]

Also, have a go at using Rick’s code, he has listed the instructions. It provides exactly what you asked for in the original request.

Are you sure?

I am looking for a solution which avoids VBA.

 

[Detectiveclem]

Thank you Rick, this works perfectly. As the VBA sits in the background I am sure it will just be perfect. And thank you for your very clear instructions. One question will I need to add to the code every cell i want this to happen with (I have about 100!!!).
Thank you so much again.
Paul