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.

 

[Joe4]

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

I am not familiar with that.
What version of Excel are you using?
Where do you find that?
Can you see the VBA code behind it?

 

[Fluff]

The Form needs to be added to the ribbon as it's not part of any of the tabs as standard.
Also there is no way (that I know of) of changing it and when using it to delete/change cells the change event is not triggered.

 

[De Bruce]

I am not familiar with that.
What version of Excel are you using?
Where do you find that?
Can you see the VBA code behind it?

it's Office 16, actually it is a default Data Form created in Excel. You can add it to ribbon tab via the option menu.

 

[De Bruce]

The Form needs to be added to the ribbon as it's not part of any of the tabs as standard.
Also there is no way (that I know of) of changing it and when using it to delete/change cells the change event is not triggered.

Thanks Fluff, perhaps I'll work on the sheet without the Default Entry Form.

 

[De Bruce]

I wanna thank everyone for their suggestion and assistance. It was very helpful. with love from De Bruce

 

[DanteAmor]

How about:

Sub replace_cell()
  Dim rng As Variant
  On Error Resume Next
  Set rng = Application.InputBox("Select cells by pressing Control or Shift", "Select", Selection.Address, Type:=8)
  If rng Is Nothing Then Exit Sub Else rng.Value = "xxx"
End Sub

 

[De Bruce]

How about:

Sub replace_cell()
  Dim rng As Variant
  On Error Resume Next
  Set rng = Application.InputBox("Select cells by pressing Control or Shift", "Select", Selection.Address, Type:=8)
  If rng Is Nothing Then Exit Sub Else rng.Value = "xxx"
End Sub

Thanks Dante,
Will I place the code in the Worksheet_Change event or a module?

 

[DanteAmor]

Thanks Dante,
Will I place the code in the Worksheet_Change event or a module?

In a module, you run the macro, select the cells you want to delete.

 

[De Bruce]

In a module, you run the macro, select the cells you want to delete.

It worked perfectly!!!, Thanks once again

 

[DanteAmor]

It worked perfectly!!!, Thanks once again

I'm glad to help you. Thanks for the feedback.