Replace a blank cell with a string using if function

[De Bruce]

Good day all,
I have an uncommon challenge. I have a sheet named Sheet1 filled with data, I want a situation when I delete the values in a cell, instead of remaining blank, it will be filled with a string (example "x").
I tried using the IF function from Sheet2 to fill a blank cell in Sheet1
=IF(Sheet1!A5 = "", Sheet1!A5 = "X"). I keep getting an error FALSE from the cell I inserted the formula.
Is there any the get this right.
Thanks in advance.

 

[Fluff]

A formula can only affect the cell that's it in.
To do what you want would need VBA

 

[De Bruce]

Thanks for the suggestion Fluff,
Any ideas on the code I could use?

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
    Application.EnableEvents = False
    
    For Each cell In Target
        If cell.Value = "" Then cell.Value = "x"
    Next cell
    
    Application.EnableEvents = True
    
End Sub

This code will automatically populate an "x" in any cell on that sheet that you manual delete the data out of.

 

[De Bruce]

Thanks Joe4, the codes worked perfectly well

 

[De Bruce]

It works manually. However, how can I adjust the variable so It can automatically populate an "x" when deleting from a data entry form. Thanks once again.

 

[Joe4]

How exactly are you deleting it from a data entry form?
What does the code behind the data entry form look like?
That is probably where you will need to address it.

 

[De Bruce]

It is the default data entry form that comes with excel. The data entry form is a faster way of imputing and deleting data in a cell. When I delete via the data entry form the x doesn't appear in the cell

 

[kweaver]

I may be off base, but took a different approach with the following that prompts you to enter the cells you want "deleted" and replaced with "xxxxx"...
Delimit the cells with a comma, such as: B3,C4

Sub Obfuscate()
Dim strg As String, nDelim As Integer, i As Integer, w As String, p As Integer
strg = InputBox("Cells to blank out?", "Obfuscate")
strg = strg & ","
nDelim = Len(strg) - Len(Replace(strg, ",", ""))
For i = 1 To nDelim
 p = WorksheetFunction.Find(",", strg)
 w = Left(strg, p - 1)
 Range(w) = "xxxxx"
 strg = Right(strg, Len(strg) - p)
Next i
End Sub

 

[De Bruce]

Thanks Kweaver, that's what I'm using right now. thanks