automatic regeneration of formulas once information in cell is overwritten

[uniquo]

It is like what was done in this thread : http://www.mrexcel.com/forum/showthread.php?t=432212

However I want to do the same except with formulas, but I don't want the formulas visible like in the post thread above, where it shows "Type prosthesis here". I only want the formulas to regenerate if what is orignally in the cell is overwritten then deleted.

Example

column
-----A
row 1--=B2*c3

formula in cell A1 = B2*C3 is overwritten intentionally with digit 3, then the user discovers this information is wrong. The user then deletes the digit 3 and then in cell A1 the formula = B2*C3 automatically comes back.

Thanks

 

[Blade Hunter]

Something like this??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Text = "" And Target.Address = "$A$1" Then Target.Formula = "=B2*C3"
End Sub

Paste to the sheet level in the VBE

 

[uniquo]

THankyou sooo much.. But how do I merge it with the formula in the thread above?

That macro is based on cases. How do I change merge your formula with it?

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target = "" Then
Select Case Target.Address
Case "$F$45": Target = "Type Prosthesis Here"
Case "$F$46": Target = "Type Prosthesis Here"
Case "$F$47": Target = "Enter Item here & amount in total charge"
'etc
Case Else:
End Select
End If
Application.EnableEvents = True
End Sub

 

[Blade Hunter]

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target = "" Then
Select Case Target.Address
Case "$F$45" Target = "Type Prosthesis Here"
Case "$F$46" Target = "Type Prosthesis Here"
Case "$F$47" Target = "Enter Item here & amount in total charge"
Case "$A$1" Target.Formula = "=B2*C3" '<----New entry

'etc
Case Else
End Select
End If
Application.EnableEvents = True
End Sub

 

[uniquo]

Is the ' between C3" and < a typo or intentionally put there?

 

[Blade Hunter]

It's a comment (preceded with a ') this tells the code that it is purely for your reference and will not effect the code. I put it in there to show you what I added, you can leave it in or take it out, it makes no difference to the code. If you take it out you need to take out everything after it too.