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

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
320
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.
 
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
Thank you for all your help Taul, as you have mentioned what Rick has shown appears to resolve my issue. I have just asked him one further question. Thanks again.

Paul
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
One question will I need to add to the code every cell i want this to happen with (I have about 100!!!).
That depends. I might be able to do something to help minimize the work for you, but I would need to know how those 100 or so cells are "arranged" in order to know what. Are they contiguous (for example, A2, A3, A4 and so on)? If not, are they regularly spaced with respect to each other (for example, A2, A6, A10, A14, etc. where each is 4 cells from its nearest "neighbor")? Or are they scattered all about with no rhyme or reason to the placement?
 
Upvote 0
Hi Rick, this is exceptionally kind of you.

All the cells from D8 to D194 require this.

With exception of With exception of D20, D22, D27, D28, D30, D35, D36, D38, D43, D44, D45, D49, D57, D58, D64, D65, D66, D67, D76, D78, D85, D87, D89, D92, D97, D98, D102, D104, D108, D110, D111, D114, D116, D124, D125, D127, D130, D135, D136, D137, D140, D142, D143, D147, D151, D156, D157, D160, D162, D165, D166, D167, D172, D174, D176, D181, D184, D188, D191, D192

The text varies throughout although most state "Leave blank if not applicable". The exception cells I have shown above contain either text which I don't want changed or have a dropdown list for the cell.

I hope this all makes sense, as I say your help is extremely grateful.

Thank you, Paul
 
Upvote 0
The easiest way to do this would be to place all of your default text into cells D8:D194 on a separate sheet (you can hide the sheet if you do not want it to be seen) which will serve as a template for the code to use. For the example code below, I have assumed you will name this separate sheet CodeTemplate. Once you have entered all the text in those cells (I suggest you copy "Leave blank if not applicable" into all the cells an then overwrite the exception cells with the text they should hold), you can then replace the code I gave you earlier with this code and all should work as needed...
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim Colr As Long, Txt As String
  
  If Target.Count = 1 Then
    If Not Intersect(Target, Range("D8:D194")) Is Nothing Then
      Txt = Sheets("CodeTemplate").Range(Target.Address).Value
    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
 
Upvote 0
Yeah, I forgot about that bit from the OP, I guess we are all open to persuasion.
 
Upvote 0
:oops: Hmm, seems like I interpreted that original request too rigidly. Thankfully Rick didn't. ?
Hi Peter_SSs, No you didn't, ideally I hoped to get a fix without using a Macro, but as Taul and Rick have very kindly provided assistance I assumed what I was asking for was not possible with a macro. The solution that Rick has come up with appears to perform the task I was originally asking for. As long as the numerous outside users open it as a Macro enabled spreadsheet then everything will be fine (My concern would be that they only open it without the Macros . For that I am extremely grateful for all the help I have received, but obviously if you have another solution I would love to hear from you. Thank you, Paul
 
Upvote 0
The easiest way to do this would be to place all of your default text into cells D8:D194 on a separate sheet (you can hide the sheet if you do not want it to be seen) which will serve as a template for the code to use. For the example code below, I have assumed you will name this separate sheet CodeTemplate. Once you have entered all the text in those cells (I suggest you copy "Leave blank if not applicable" into all the cells an then overwrite the exception cells with the text they should hold), you can then replace the code I gave you earlier with this code and all should work as needed...
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim Colr As Long, Txt As String
 
  If Target.Count = 1 Then
    If Not Intersect(Target, Range("D8:D194")) Is Nothing Then
      Txt = Sheets("CodeTemplate").Range(Target.Address).Value
    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
Hi Rick, sorry for the delay coming back to you. Your solution appears to work like a treat, but I am still changing the text on some of the questions. But when I tested it, it worked amazingly well.
One further question, is it possible to add to the code something which stops the person from moving to the next question if the question they are at is mandatory. But not all questions are mandatory. I know this is a big ask, but you clearly have fantastic Excel skills. Thank you so much Paul
 
Upvote 0
I am not sure I understand how that would work. Can't the user go to any cell they want in any order? Are you saying if they select a "mandatory" cells that they should remain trapped in it until they type something in it? Wouldn't that be why they picked the cell in the first place? What about if they accidentally selected one of these "mandatory" cells... they would be trapped in it even if they were not ready to fill it in? You are going to have to tell us much more about how you want this to work before we can begin to write code to implement it.
 
Upvote 0
I am not sure I understand how that would work. Can't the user go to any cell they want in any order? Are you saying if they select a "mandatory" cells that they should remain trapped in it until they type something in it? Wouldn't that be why they picked the cell in the first place? What about if they accidentally selected one of these "mandatory" cells... they would be trapped in it even if they were not ready to fill it in? You are going to have to tell us much more about how you want this to work before we can begin to write code to implement it.
Hi Rick, thank you for the very quick reply. What I am drafted is a questionnaire where some fields are required to be filled, for example the name and address fields, but also other fields where they need to answer a particular question. I have come across these types of questionnaires where the user is lead from one question to the next, but certain questions must be answered before the can move forward. I hope I have explained myself, but if you need anything further please let me know.
I really appreciate what you have done already and what you may be able to do going forward,

Thank you,

Paul
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top