worksheet function - EXCEL - How to have a formula output and user input on the same cell?

I would like help from someone as I believe this problem requires VB. I basically have a CELL(A4) where people will be entering values to find out the corresponding calculations displayed below.

However I want to add an additional function where popular values are automatically entered into that same cell(A4).

Cells E7 and E8 will receive input and multiply it by 960(E7) or 765(E8) and transfer that value to cell(A4) so the rest of the formulas update properly.

Basically have the user input the bags directly in A4 or enter the number of containers in E7 or E8.

**This is where i have the problem because when I enter a formula on A4 it gets deleted once you enter a value directly on it.

**Also note that the result from E7 and E8 should be displayed (in A4) as I need it as well.

My sheet:

Answer

You can use a worksheet change function in vba. Something like

Private Sub Worksheet_Change(ByVal target As Range)

If Intersect(target, Range("e7:e8")) Is Nothing Then Exit sub

Application.enableevents = False
ActiveSheet.range("A4").formula="=if(e7="""",e8*765,e7*960)"
Application.enableevents = True

End Sub

Anytime the sheet changes it checks if the cell changed was e7 or e8. If it was it puts the formula

=if(e7="",e8*765,e7*960)

in cell A4. If you then put a value in cell A4 it will stay until you again change e7 or e8. You may need to adjust to get it to do exactly what you want but the idea is there.

This would go in the worksheet object under microsoft excel objects in vba.

EDIT:

To fit in with comments.

New code, this one will only do something if the cell has a value added or changed, it won't run if you delete a value.

Then it checks if e7 was edited. If it was e8 is deleted. If it wasn't we know e8 was edited so e7 gets deleted. Formula stays the same.

Private Sub Worksheet_Change(ByVal target As Range)

    If Intersect(target, Range("e7:e8")) Is Nothing Or target.Value = "" Then Exit Sub
        Application.EnableEvents = False

        ActiveSheet.Range("A4").Formula = "=if(e7="""",e8*765,e7*960)"

        If Intersect(target, Range("e7")) Is Nothing Then
           ActiveSheet.Range("e7").ClearContents
        Else
            ActiveSheet.Range("e8").ClearContents
        End If

    Application.EnableEvents = True

End Sub