Trying to hide text in an excel cell

Question

I am looking at running test on survey results. For the column headers for each question they are IMP1, IMP2, etc. What I want to be able to do is place the question in this cell so that when you click on the header you can see the question but from the overview of the file all the user can see is IMP1.

Not sure if that wording makes sense but basically I want the text in the formula section when you click on a cell. When the cell isn't selected it should just show IMP1.

Answer 1

This is specifically for the single cell A1, but can be expanded to process all the cells in column A. First enter this in the cell:

IMP1What is the meaning of life ??

and then place the following Event Macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim A1 As Range

    Set A1 = Range("A1")
    l = Len(A1.Text)

    If Intersect(A1, ActiveCell) Is Nothing Then
        A1.Characters(Start:=1, Length:=l).Font.ColorIndex = 1
        A1.Characters(Start:=5, Length:=l).Font.ColorIndex = 2
    Else
        A1.Characters(Start:=1, Length:=l).Font.ColorIndex = 1
        A1.Characters(Start:=1, Length:=4).Font.ColorIndex = 2
    End If
End Sub

If you click on A1, you will see:

and if you click off the cell, you will see:

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Answer 2

Besides using Comments you could use a VBA subroutine to do this based on the worksheet's SelectionChange event. In your VBE, double click the worksheet where this event is taking place in the VBAProject pane. In that code window place the following:

'Global Variable to hold the last column A cell that was clicked into
Private lastClicked As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Did we click out of a column A cell?
    If Not lastClicked Is Nothing Then
        If Target <> lastClicked Then

            'Copy the holder value from column C back to Column A
            lastClicked.Value = lastClicked.Offset(, 2).Value

            Set lastClicked = Nothing
        End If
    End If

    'Detect a click into column A
    If Target.Column = 1 Then

        'Update the global "lastClicked" variable
        Set lastClicked = Target

        'Move the holder text to column C
        Target.Offset(, 2) = Target.Value

        'Move the question text from column B to target
        Target.Value = Target.Offset(, 1).Value


    End If

End Sub

This set up is assuming that your questions (the holder text like IMP1) is in Column A of the worksheet, and that you would have the entire question hidden (I assume) in Column B. Also that Column C would be empty so that we could temporarily hold the holder text (although you could stuff that into it's own global variable as well).