VBA to show text "Enter Text Here" in excel cell

[Anshu]

I want to show "Enter Text Here" in fade color in a cell, say C5, when it is empty.

As per my knowledge, it can't be done by Conditional Formatting.

One way I found is to create a transperent Text Box Mask linked with other cell. But masking text box is time consuming, especially if I have more than 10 cells to show the text.

Is there any VBA code to solve this problem??

[DocAElstein]

Hi
I am not sure if I understand fully what it is that you want…
( I do not know what a “Text Box Mask linked with other cell” is )

It would be quite easy in VBA to use an Event type coding similar to what you have seen before to make a cell have a particular text in the case that it gets changed to being empty.

For me personally, the only problem in such coding is the cell formatting syntax, since I can never remember all the different color and format options.

So my start point would be to record a macro ( https://de.lmgtfy.com/?q=vba+using+the+macro+recorder
https://de.lmgtfy.com/?q=using+the+m...hindi+tutorial )
whilst putting text as I want in an arbitrary cell

For example, I recorded a macro whilst putting in some text in a cell , first by pasting it in, and then by wriring it in. In both cases I changed the text color.

This what the macro recording produced

Sub Macro1()
'
' Macro1 Macro
    ActiveSheet.Paste
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
    End With
End Sub
Sub Macro2()
'
' Macro2 Macro
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Enter Text Here"
    Range("B1").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
    End With
End Sub

So I have now some basic formatting information

Thinking ahead, I also may need to change the format back to normal, if text is entered into a cell. So I recorded a macro whilst doing that, and got this

Sub Macro3()
'
' Macro3 Macro
    Range("C2").Select
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
End Sub

---

This might help you get started on coding to do what you want. This will need to go in a worksheets object code module, as you have been familiar with in the past.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "" Then  '  case a cell was emptied
     Let Application.EnableEvents = False
     Let Target.Value = "Enter Text Here"
     Let Application.EnableEvents = True
        With Target.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        End With
    Else  '  case a text was entered
        With Target.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        End With
    End If
End Sub

Alan

[Anshu]

Superb! Thank you!

If I want to use the VBA for multiple cell, say B5, D9, M4 and H3, then, what change I should make in the VBA code??
(Actually I want to use the code for more than 15 cells in a single sheet)

[DocAElstein]

Currently the macro is working on all cells.
If you want to restrict it to just specific cells, then we have a few ways that we have done that
For example

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$5" Or Target.Address = "$D$9" Or Target.Address = "$M$4" Or Target.Address = "$H$3" Then
        If Target.Value = "" Then  '  case a cell was emptied
         Let Application.EnableEvents = False
         Let Target.Value = "Enter Text Here"
         Let Application.EnableEvents = True
            With Target.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            End With
        Else  '  case a text was entered
            With Target.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            End With
        End If
    Else
    ' Target is Not a cell to be acted on
    End If
End Sub

---

(Actually I want to use the code for more than 15 cells in a single sheet)

For a lot of cells, this other way we have done before may be a bit neater

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("B5,D9,M4,H3")) Is Nothing Then
        If Target.Value = "" Then  '  case a cell was emptied
         Let Application.EnableEvents = False
         Let Target.Value = "Enter Text Here"
         Let Application.EnableEvents = True
            With Target.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            End With
        Else  '  case a text was entered
            With Target.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            End With
        End If
    Else
    ' Target is Not a cell to be acted on
    End If
End Sub

[Anshu]

Thank you so much!!!
This is perfectly what I was looking for.

In between, may I know how can I mark the thread as SOLVED!

[DocAElstein]

We don't have any system of marking Threads as SOLVED at excelfox
Just remember to tell us that a solution worked, and possibly thank all the people that helped you, as you have done here.