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.
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi,
Just my two penneth, did you save the file as xlsm (macro enabled)?
You mentioned it worked last Thursday on the original workbook and again earlier today on a fresh workbook! Were they xlsx or xlsm?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
274
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Where did you put it? Basically, there are three modules where code can be placed... a general module where macros and functions are placed, the ThisWorkbook module where event code for any or all workbook-wide event code procedures go and sheet modules where sheet specific event code procedures go. The code I gave you is a sheet level event procedure so it must go in the module for the specific sheet where it will function at. The easiest way to get to the correct sheet module is to select the worksheet that the event code is meant for, then right-click its tab and select "View Code" from the popup menu that appears.
Hi Rick, thank you for this. However what you have mentioned I have tried. I can see the code you gave me, but it no longer seems to work. I have even tried copying my worksheets and trying it again, but still nothing. Is there a way I can remove all the code and then copy your code back in? Thanks Paul
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
274
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi,
Just my two penneth, did you save the file as xlsm (macro enabled)?
You mentioned it worked last Thursday on the original workbook and again earlier today on a fresh workbook! Were they xlsx or xlsm?
Yes saved as Macro enabled
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
I have even tried copying my worksheets and trying it again, but still nothing.
Grasping at straws here. Try executing this line of code in the Immediate Window and then see if my code works again...
VBA Code:
Application.EnableEvents = True
 

Detectiveclem

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

ADVERTISEMENT

Grasping at straws here. Try executing this line of code in the Immediate Window and then see if my code works again...
VBA Code:
Application.EnableEvents = True
Hi Rick,

I think I have found the issue. The code worked today, until I protected the sheet and the debug come up. I Hope this helps explain why I seem to be having issues. I need to protect the sheet and would appreciate you help to resolve this. Thanks again Paul

Capture.PNG
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
While waiting for Rick's suggestions...
Add in the same vba module that hosts your Worksheet_Change macro the following new code:
VBA Code:
Private Sub Worksheet_Activate()
Me.Unprotect Password:="MyPassword"                         '<<< Your Passw
Me.Protect Password:="MyPassword", UserInterfaceOnly:=True  '<<< Your Passw
End Sub
This way, whenever the sheet is activated is will be protected "form the user" but the macro have free access to the sheet

Bye
 

Detectiveclem

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

ADVERTISEMENT

While waiting for Rick's suggestions...
Add in the same vba module that hosts your Worksheet_Change macro the following new code:
VBA Code:
Private Sub Worksheet_Activate()
Me.Unprotect Password:="MyPassword"                         '<<< Your Passw
Me.Protect Password:="MyPassword", UserInterfaceOnly:=True  '<<< Your Passw
End Sub
This way, whenever the sheet is activated is will be protected "form the user" but the macro have free access to the sheet

Bye
Hi Anthony47,

Thank you for trying to help, I inserted your code in the unprotected sheet (as below) using the password "MyPassword1" however when I protect the sheet I still run into problems as shown from the screenshot. Have I entered the code in the right place, or is there anything else I need to do to get it to work?

1606890873820.png
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
To prevent confusion: is MyPassword1 the key YOU (forget about the code) use to protect & unprotect your worksheet?
Also, the added code works when the worksheet become activated; so after you insert the code you have at least to deactivate and reactivate the sheet.
And which problem arise with the highlighted instruction?

Bye
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
274
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
To prevent confusion: is MyPassword1 the key YOU (forget about the code) use to protect & unprotect your worksheet?
Also, the added code works when the worksheet become activated; so after you insert the code you have at least to deactivate and reactivate the sheet.
And which problem arise with the highlighted instruction?

Bye
Hi Anthony47, Thank you for your quick response, yes that is the password I have used on the sheet to protect it. Not sure what happens, just get a message saying Debug error and when I look at it the field in yellow is highlighted.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,114
Messages
5,622,811
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