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.

 

[Detectiveclem]

Beware: I edited the message maybe while you was reading it

Hi Anthony47, have you amended the code?

 

[Anthony47]

MY SUGGESTION WAS INAPPROPRIATE, please remove the block of instructions that I gave you.
On the 4th page of such a long discussion I got lost and confused the "Worksheet_Change" event that Rick's code uses for a "Worksheet_SelectionChange".
My macro was intended to give the macro controll over the worksheet while keeping the worksheet protected; but of course if the worksheet is protected the user cannot input data.

Sorry for the time you wasted following me...

And "No, I didn't amend the code"; I just added some words to the edited message

Anthony

 

[Anthony47]

Not sure what happens, just get a message saying Debug error and when I look at it the field in yellow is highlighted

You mean that a pop up arise without any text in it?
Could you make a screenshot with that error message?

Bye

 

[Detectiveclem]

Thanks Anthony47.

Here you go

This is what I see if I press the Debug button

 

[Anthony47]

Oh, the ubiquitous error 1004...
Modify the code as follows:

      End If                                    'existing code
      Debug.Print Target.Address, Target.Cells(1, 1).Value 'Added
      Target.Font.Color = RGB(0, 0, 0)          'Added
'      Target.Font.ColorIndex = 1               'Commented, ie not working
    End If                                      'existing code
'... more lines

Then try...
If you get the error and you enter the debug mode, open the vba "Immediate window" (use Contr-g; or Menu /View /Immediate window) and read which is the content.
Copy the last lines in your next message.

Bye

 

[Detectiveclem]

I have added the code you sugested as shown below. I hope I have followed your instructions correctly?

I no longer get any error messages, but the functions as set up by Rick no longer work. I really appreciate you trying to help me.

 

[Anthony47]

No, I am sorry for confusing you ...more than what I planned to do
Remove all the code you have on that vba module and insert this (Rick's code with my modification):

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
      If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57")) Is Nothing Then
        Target.Value = Application.Proper(Target.Value)
      ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then
        Target.Value = UCase(Target.Value)
      End If                                    'existing code
      Debug.Print Target.Address, Target.Cells(1, 1).Value 'Added
      Target.Font.Color = RGB(0, 0, 0)          'Added
'      Target.Font.ColorIndex = 1               'Commented, ie not working
    End If                                      'existing code
    Application.EnableEvents = True
  End If

End Sub

In other words, I changed few lines in the area that was failing

When you get the error, open the "Immediate window", read which is there listed, copy the last lines in your next message here on the forum

Bye

 

[Detectiveclem]

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 If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57")) Is Nothing Then Target.Value = Application.Proper(Target.Value) ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then Target.Value = UCase(Target.Value) End If 'existing code Debug.Print Target.Address, Target.Cells(1, 1).Value 'Added Target.Font.Color = RGB(0, 0, 0) 'Added ' Target.Font.ColorIndex = 1 'Commented, ie not working End If 'existing code Application.EnableEvents = True End If End Sub

Sorry for being dense here, but what do you mean by the "immediate window". I have entered the above code and currently at the Degug Run-time error '1004! as below. What do you want me to do now?

 

[Anthony47]

Ok
When the error is shown press Debug to enter the Debug mode; the line in error will be highlighted (confirm which line is in error); now press Contr-g, and the "Immediate window" will show up; this is part of the vba editor.
There should be some text in the window (an address and a value, at least).
Now copy what you find in that window and put it into your next message.

Also:
-you say that your sheet is protected; how the unprotect /protect process is managed? Is that manual, using the commands in the ribbon, os is that via maco?

Bye

 

[Detectiveclem]

Sorry for all this. Here is the screen shot. I manually password the sheet to protect it. The Immediate window is shown at the bottom. I hope this helps you with my problem. Thank you