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
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.
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
In business, it is common to want to use a formula to calculate a value, but still allow the user to override that formula for special cases.
Say A4 represent Total Cost and contains:
where D8 is the unit cost and G8 is the quantity.
But for special customers we want to be able to quote a discounted cost. We put the discounted cost in H8 and modify the formula thusly:
So if we want the override, it goes in H8; otherwise we leave H8 blank.
You can use VB to change any value during calculation stage.
But doing so means that the macro will change the value in A4, and you’ll have to add something to know that the value should not be updated on a second go.
So in theory it is possible, but it is highly impractical to do so.
If you purely want to have a dropdown list with popular values, you do not need to go fancy VB. Excel has this function built in. You can do this through
The solution i use where you have only a few cells that i want both calculations and the abillity to enter values to override the formula or delete what they entered and have the formula work is.
Setup your worksheet as you want..
Then for the cell you want to override setup an identical cell in a column off screen, ie column az and cut and paste your ell there
In this special enter, alter your formula but with an if to test if a value has been keyed in the orginal cell.
Then copy this cell , select your orginal cell and paste special a linked picture.
Now select the picture and add a hyperlink to the cell directly below it, use a ranbe name if you think it may move due to rows being inserted.
Now you should find the cell looks like it is displaying a value from the formula, when you click the cell the hyperlink jumps to the cell underneath enter the value and the display changes, delete it and the formula value displays.
Also consider adding a conditional format on the formula cell that changes if there is a value in the displayed cell, this helps remind you a manual value has been entered.
Hope that helps setup forms that look simple and perform without macros.