Excel Cell Programming

Question

This may be a question for stackoverflow I'm not sure,

I would like to know if it is possible to program a cell in Excel 2010 to have text that disappears once you click into the cell. The sort of programming you would apply to a text input block on a html form.

I would like to do this as I require a way in which to inform a user of what to place in the cell. I am aware that comments can do this effectively but I would like the text to be present without prompt with mouse hovering or the Show all comments button.

Would I use scripting to do this? Or is there a feature in Excel?

Answer 1

You can clear a specific cell by using VBA's SelectionChange() method.

Private Sub Worksheet_SelectionChange(ByVal ActCell As Range)
    Set TarCell = Sheets(1).[B3]
    If ActCell.Address = TarCell.Address Then TarCell.Clear
End Sub

For this example, write something into our target cell [B3] and reselect the cell again.
The macro will be executed on every new selection a user made. Next, it checks if the new selected cell is your desired target cell TarCell. If yes, clear its default text.

Answer 2

Try this forum topic: http://www.mrexcel.com/forum/excel-questions/184269-placeholder-text-disappear-when-cell-filled.html

"use Data Validation so that when a cell is selected a brief description of what should go in that cell will be displayed. Go to Data\Validation\Input message and enter your text"

Answer 3

I think you are over-thinking the problem. Excel already has built in facilities for this.

When you have a cell selected and start to type, then the text that was already in the cell gets cleared automatically. It doesn't get cleared on click, as you would like, but it will still give you the result you are looking for.

It's perfectly acceptable in an excel file to have a cell that says "Enter Value X Here". Then someone clicks that cell and starts typing. No need for macros or anything else to complicate your document and confuse your users (people will get surprised and not trust your document when text starts disappearing every time they click somewhere, and they might forget what your hint said by the time they click on the cell).

This is functionality people using excel already know. No need to teach them something new.

Answer 4

You cannot do this with formulas, but you can do this with VBA for Excel.

The general idea is:

1. You have a routine to set up your worksheet. As you do that you set certain cells to contain values that will serve as cues. This routine can be called with a click of a button to reset the sheet or to copy it into a new sheet for example.

2. When users edit the sheet then for those cells you set up initially (think of them as ranges of 1 cell each) you define what to do when the cell changes.

   Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Me.Range("H5")) Is Nothing Then
         'this supposes on current worksheet the range of interest is H5
         'Your VBA code here
         'you will want to set range value to default value if it is empty string "" 
         'or do something else 
      EndIf 
   End Sub