Default Formula for a Cell

W

wrnoof

I am not sure if this question belongs in the programming forum. Please
direct me elsewhere if appropriate.

I am sure I have seen workbooks that display a calculated result in a cell
but allow the user to over ride that result with a manually entered value. If
the manual value is removed, the calculated result reappears.

Is this possible or did I imagine it? If possible, how is it done?

wrnoof
 
J

Jacob Skaria

You are in the correct forum..

You can achieve this with the help of worksheet change event. Try the below
example which will assign a default formula in cell A3; and allow user to
override this with a different value..If the cell is blanked the formula
returns...To install the below code; right click the sheet tab>View Code and
paste the below code in the code module.Try this in the active sheet and
feedback


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A3")) Is Nothing Then
If Target = "" Then Target.Formula = "=A1+A2"
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
 
W

wrnoof

I pasted the code into the Visual Basic editor, closed the editor, inserted 1
in A1, 2 into A2 but nothing appeared in A3 when I was expecting "3". I am
sure your code is correct, but I am a true novice at Visual Basic and
probably missed something. Thanks for leading me through this.
 
G

Gord Dibben

Select A3 and enter any number.

Now delete that number.


Gord Dibben MS Excel MVP
 
W

wrnoof

Sometimes it's the simplest things. That did it, thanks.

Also, I had just installed Excel 2007 and the option that disables macros
was turned on. That needed to be changed, also. It showed up in the toolbar
or ribbon, so I didn't need to go looking for it.

Thanks again to both of you.
 
Top