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

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
274
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.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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:
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
    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
 
Solution

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
274
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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:
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
    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

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
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

Active Member
Joined
May 31, 2014
Messages
274
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,117
Messages
5,622,821
Members
415,934
Latest member
adstocking

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
Top