Instructions to be displayed in the background of a cell when empty

[Moutishake]

Hello everyone,

So I have been trying to do something in excel for some time without success.
I have found quite a few other posts online of people trying to do similar things, but didn't succeed.

I must say my VBA knowledge is non existent.

So what I would like to do:
I created a form in excel that will have to be filled on the job.
A previous form was used in the past, and one of the problem was the dates field. sometimes several dates format where used on the same form, which is uncomfortable.

So I would like the date cell to display, in light grey maybe in the background, the preferred format for the date, MM/YYYY, when the cell is empty.
But when data is entered, this instruction would disappear. And reappear again if the cell content is deleted

Thanks in advance to anyone who can help me with this

 

[Fazza]

welcome

maybe data validation? it is built in & requires no code. ALT-D-L and then in the 'input message' tab you can write a message

regards

 

[Jonas Offersen]

Hi! it sounds like you're using cell and textbox interchangeably? Do they type data into a cell on the workbook or do they type it into a textbox on the userform?

In case of the latter, this is working on my search text boxes:

Private Sub TextBox1_AfterUpdate()
    With TextBox1
        If .Text = "" Then
            .ForeColor = &HC0C0C0 '<~~ Grey Color
            .Text = "Søg"
        End If
    End With
End Sub

Private Sub TextBox1_Enter()
    With TextBox1
        If .Text = "MM/YYYY" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .Text = ""
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    TextBox1.ForeColor = &HC0C0C0 '<~~ Grey Color
    TextBox1.Text = "MM/YYYY"

 

[Moutishake]

So far I have only cells in form.
After posting I kept on trying and put a bit of code which was working, but when saving closing and opening again the form act like there was no code.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target.Value
Case Is = ""
Target.Value = "Enter Last Name"
Target.Font.ColorIndex = 15
Case Else
Target.Font.ColorIndex = 1
End Select
End If

End Sub

If possible I'd like a solution which doesnt involve code.
This form will be use on a variety of computer with different excel version.

I am gonna look into data validation.
I also had another idea, how about a watermark on the cell background, which is excluded from printing?
What do you guys think?

 

[Moutishake]

Actually having a text box behind the cell with the instructions in grey and excluding the text box from printing via its properties did the trick

 

[Jonas Offersen]

I'm glad you found a solution! ^^ I'm sorry, I wasn't sure what you meant by "form" because usually that would refer to vba userforms. xD (I get it now, mb)

In case you want to look into it, the cells can be changed by placing this in the worksheets code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Cells(1, 1)
        If .Value = "" Then
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = -0.149998474074526
            .Value = "MM/YYYY"
        ElseIf Not (.Value = "MM/YYYY") Then
            .Font.ThemeColor = xlThemeColorLight1
            .Font.TintAndShade = 0
        End If
    End With
    If Selection = Cells(1, 1) Then
        If Selection.Value = "MM/YYYY" Then
            Selection.Value = ""
        End If    
    End If
End Sub

I had to restart excel after writing the code, to make it register the event, though.