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]

Is the worksheet (a) always protected, but cells to be compiled are unlocked; or (b) do you set the protection On and Off depending on what the user has to do?

If "a", then remove the protection and reapply it BUT check for "allow cell formatting"; then retry

If that doesn't change the situation then I would go with removing sheet protection when you start Rick's code and reapply it when completing the modifications:

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
    Me.Unprotect "MyPassword1"      '<<< UNPROTECT SHEET
    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, Me.ProtectContents 'Added
      Target.Font.Color = RGB(0, 0, 0)          'Added
'      Target.Font.ColorIndex = 1               'Commented, ie not working
    End If                                      'existing code
    Me.Protect "MyPassword1"        'PROTECT SHEET
    Application.EnableEvents = True
  End If
End Sub

I used "MyPassword1" to protect /unprotect; modify as necessary

Bye

 

[Detectiveclem]

Is the worksheet (a) always protected, but cells to be compiled are unlocked; or (b) do you set the protection On and Off depending on what the user has to do?

If "a", then remove the protection and reapply it BUT check for "allow cell formatting"; then retry

If that doesn't change the situation then I would go with removing sheet protection when you start Rick's code and reapply it when completing the modifications:

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
    Me.Unprotect "MyPassword1"      '<<< UNPROTECT SHEET
    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, Me.ProtectContents 'Added
      Target.Font.Color = RGB(0, 0, 0)          'Added
'      Target.Font.ColorIndex = 1               'Commented, ie not working
    End If                                      'existing code
    Me.Protect "MyPassword1"        'PROTECT SHEET
    Application.EnableEvents = True
  End If
End Sub

I used "MyPassword1" to protect /unprotect; modify as necessary

Bye

Anthony47, you are an absolute star. This now works perfectly. Words can't really express my gratitude, but thank you for persevering. You have been so helpful and thank you again.

 

[Anthony47]

99% it was Ricky's effort.
But wich hypotesis worked for you? Enabling Cell format on worksheet protection, or remove /rest protection within the code?

Bye

 

[Detectiveclem]

99% it was Ricky's effort.
But wich hypotesis worked for you? Enabling Cell format on worksheet protection, or remove /rest protection within the code?

Bye

Yes, I Agee Rick put together the magnificent code to start with, but you have helped me to use Rick's code on a locked sheet. So pleased by both of your efforts. Thanks again, Paul

 

[EXCELLENTOFFICER]

A much simpler solution that I used was conditional formatting. I added a rule to format the text to be gray if a certain word was in the cell. For example if the cell contained "name" to format the text to be gray in color. I then highlighted the cells and changed the font color to black. So once I filled in the cell with something other than "name" the text would automatically change to black.