Dissapearing Message

[Scafidel]

I have an Excel form type sheet that employees complete which fills other
sheets for printing. How could I have cells with instructive text that would
disappear when clicked on? Like what is used on HTML, i.e., a cell might
contain "Name," another "State," another "Zip," etc.
Thanks

 

[Gary''s Student]

Say we want cell B9 to display "Name" and we want that text to vanish if the
user clicks on B9.

First Insert a hyperlink in B9 to a place in this document, namely B9, and
make the display text "Name". Do not user the =HYPERLINK() function.

So if the user clicks on B9, the cursor jumps to B9 (not very impressive,
huh?)

Then insert the following tiny event macro in the worksheet code area:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Target.Range.Clear
End Sub

Now:

1. the user see the text "Name" in B9
2. the user click on B9
3. the hyperlink activates
4. the macro runs and clears B9
5. the user types data into B9

 

[Scafidel]

Thanks, GS, it worked, but once it was used, I had to reinstall the hyperlink
each time. Is there anyway to have it restore the hyperlink, if used and
then emptied?

 

[Gary''s Student]

It is possible, but more difficult. The code will have to "remember" the
contents of the cell before clearing it. The code could then use this stored
value to re-populate the hyperlink once the user has cleared the cell.