How to have instructional text in Excel?

Question

I would like to know how to place instructional text (like placeholder text) that disappears on click in some Excel cells (C9, C21, C58, C96).

I would need the instructional text to be gray and the real text to be black.

Is there a way to do this?

Answer 1

One way to achieve this would be to use an ActiveX textbox that is linked to a cell instead of a cell directly (See at the end for the approach using a cell only).

Here's a demo of how this can look like:

Here's the code I had in the Worsheet code module:

Const PLACEHOLDER_TEXT = "My placeholder text"

Private Sub TextBox1_GotFocus()
    'Clear text content
    With TextBox1
        If .Text = PLACEHOLDER_TEXT Then
            .Text = ""
            .ForeColor = vbBlack
        End If
    End With
End Sub

Private Sub TextBox1_LostFocus()
    'Add the placeholder text if user exits without filling the field
    With TextBox1
        If .Text = vbNullString Or .Text = PLACEHOLDER_TEXT Then
            .Text = PLACEHOLDER_TEXT
            .ForeColor = vbGrayText
        End If
    End With
End Sub

And to add a linked cell, you can enter Design Mode, right-click the textbox > Properties > Edit the Linked Cell entry using the standard Excel address format:

---

For the approach with a cell and using Worksheet_SelectionChange as suggested by BigBen, you could use something like this inside the worksheet module:

Option Explicit

Const TARGET_CELL_ADDRESS As String = "C2"
Const PLACEHOLDER_TEXT As String = "My placeholder text"

Const GREY_COLOR = 10921637
Const BLACK_COLOR = 0

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Replace(Target.AddressLocal, "$", "") = TARGET_CELL_ADDRESS Then
        'Clear text content
        With Target
            If .Value2 = PLACEHOLDER_TEXT Then
                .Value2 = ""
                .Font.Color = BLACK_COLOR
            End If
        End With
    Else
        'Restore placeholder text if needed
        Dim Rng As Range
        Set Rng = Me.Range(TARGET_CELL_ADDRESS)
        With Rng
            If .Value2 = vbNullString Then
                .Value2 = PLACEHOLDER_TEXT
                .Font.Color = GREY_COLOR
            ElseIf .Value2 = PLACEHOLDER_TEXT Then
                If .Font.Color <> GREY_COLOR Then
                    .Font.Color = GREY_COLOR
                End If
            End If
        End With
    End If
        
End Sub