Add text instruction to a cell to prompt for input

[Rocky2013]

Hello,
In A1- A4, I want to have a text prompt (gray color, Arial) in each cell
to ask the user to enter the information. The text prompt will be replaced
by the information you entered. Delete the information, the prompt will reappear.

Cell ......Prompt
A1 = Enter your name
A2 = Street number, Street
A3 = City, State, Zip Code, Country
A4 = Telephone Number

I have being using two cells to do this but problematic if there are lots, and it does
not work for merged cell. What I did is that the cell on the left, I make the column width very narrow, and I place
a formula, to check the 2nd cell on the right if it is "" or not "". If it is "", then "Enter your name";
if this not "", then "".

I use across horizontal alignment on both cells to position the prompt on the 2nd cell. Because
1st cell is very narrow column width (1), the text appears is belong to the 2nd cell.

I am looking for a new way using VBA to do this. Cells can be any where other than A1:A4.
It is better if VBA can be written to allow me to specify the cells to use the code.


Thanks

[gmr4evr1]

See if the attached is close to what you are wanting
Rocky2013 Add text instruction to a cell.xlsm
Here is the code I used

Please Login or Register  to view this content.

[mikerickson]

You could use Data Validation, with the formula =TRUE, and set the InputMessage to the text you desire. No VBA is needed.

[leelnich]

Hi Rocky, I really like your 2-cell trick. If you left-align the column A "prompts", you can indent several times to make the text appear to occupy column B, WITHOUT making column A "skinny" OR needing a formula (prompt only appears if the cell to the right is empty). Would that help?
NOTE: Make sure word-wrap is OFF in the "prompt" cell.

[Rocky2013]

Hello,
gmr4evr1, I take your code but I change the font color to gray using conditional formating.
Once the information is entered, it changes the font color to black (default). I pre

mikerickson, Do not knpw exactly how to setup.

leelnich, The reason I make the first column width very narrow to 1.0, because I want to make that column
visual disappear and save the overall sheet width. Besides those 4 prompts but there are more cells with
various column widths. In some case I have to merge two cells to make up the text width.

Thanks all.

[k1dr0ck]

its not the best but try it

Please Login or Register  to view this content.

[mikerickson]

...
mikerickson, Do not knpw exactly how to setup.
...

Look at A1:A4 of the attached.

[jeffreybrown]

You could use Data Validation, with the formula =TRUE

Hi Mike,

What's the purpose of using =TRUE in the formula of the DV? I not seeing any difference between using =TRUE or not using it, but must be missing something.

[mikerickson]

The goal is to get the Input Message, not to validate the entry.
With =TRUE, Excel will accept anything that the user enters.

The =TRUE allows us to use the Input Message feature of Data Validation, without being bothered with any data being validated.

(Kind of a cheap SelectionChange event)

[jeffreybrown]

Okay thanks Mike for the explanation.