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.

 

[Taul]

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?

 

[Detectiveclem]

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]

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]

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...

Application.EnableEvents = True

 

[Detectiveclem]

Grasping at straws here. Try executing this line of code in the Immediate Window and then see if my code works again...

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

 

[Anthony47]

While waiting for Rick's suggestions...
Add in the same vba module that hosts your Worksheet_Change macro the following new 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]

While waiting for Rick's suggestions...
Add in the same vba module that hosts your Worksheet_Change macro the following new 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?

 

[Anthony47]

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

 

[Anthony47]

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

 

[Detectiveclem]

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.