Populate cell based on another cell but without a formula in the cell to be populated

[sparky2205]

Not sure this is even possible but here goes.
I want a cell to be populated with ENTER NAME HERE unless the cell is populated with anything else.
But the cell can't contain a formula.

I'll expand a little
I have a form with a box (cell) which contains the default text ENTER NAME HERE. Nothing special about the cell.
When a user enters a name it obviously overwrites the default text.
What I want is, if the user deletes the name in the cell the default text ENTER NAME HERE automaticlly returns. i.e. if the cell is blank display the default text.

I'm looking for a non vba solution as if the form gets updated in the future which affects the cell address and I'm not around they won't have a clue how to fix it.

 

[Fluff]

Fraid you would need VBA for that.

 

[Joe4]

I'm looking for a non vba solution as if the form gets updated in the future which affects the cell address and I'm not around they won't have a clue how to fix it.

I am pretty sure that something like this would require VBA.

Without VBA, a cell can only have a formula or hard-coded text in it, and nothing will automatically change that on-the-fly.

 

[sparky2205]

I thought as much.
Thanks for confirming guys.
Have a good weekend.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sparky2205]

Good morning guys,
I've been trying to get the vba working to achieve this but I obviously don't have a proper grasp of the worksheet_change event.

As a recap, what I'm trying to achieve:
• by default the range (merged) K11:L11 contains the text ENTER NAME HERE in blue text
• when the user enters a name the text colour changes to automatic (achieved via conditional formatting)
• if the user deletes the default text but doesn't enter a name the default text is automatically reentered when they leave the cell

This is what I tried, among others:

Private Sub worksheet_change(ByVal target As Range)

    Dim n As Range
   
    Set n = Range("K11:L11")
       
    Application.ScreenUpdating = False

    If Application.Intersect(n, Range(target.Address)) _
        Is Nothing Then
        n.Value = "ENTER NAME HERE"
    End If
   
    Application.ScreenUpdating = True
   
End Sub

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("K11:L11")) Is Nothing Then
      If Target(1).Value = "" Then Target(1).Value = "ENTER NAME HERE"
   End If
End Sub

 

[sparky2205]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("K11:L11")) Is Nothing Then
      If Target(1).Value = "" Then Target(1).Value = "ENTER NAME HERE"
   End If
End Sub

Thanks Fluff,
but this doesn't work for me.
It has the same result as my own code in that it, well, doesn't do anything.
It's almost like it's not recognising the change event on the target.
I'm not gettng any errors but it's not putting in that text either.

 

[Fluff]

Try putting a Msgbox as the very fist line of code & then delete the value in K11, do you see the message?

 

[sparky2205]

I noticed some strange things had started to happen to the spreadsheet.
When I typed into one cell it appeared that it was entering data in a different cell.
So, back to basics, I started with a fresh spreadsheet and your code works perfectly.
Apologies for any confusion and thanks very much for your help Fluff. Have a good day.