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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Anthony47

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

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

Active Member
Joined
May 31, 2014
Messages
274
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Thanks Anthony47.

Here you go
1606896850995.png



This is what I see if I press the Debug button

1606896958356.png
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135

ADVERTISEMENT

Oh, the ubiquitous error 1004...
Modify the code as follows:
VBA Code:
      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

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

1606899395425.png
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135

ADVERTISEMENT

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):
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
      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

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

1606903073859.png
 

Anthony47

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

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

1606906534222.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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