Default Formula for a Cell

  • Thread starter Thread starter wrnoof
  • Start date Start date
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
 
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
 
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.
 
Select A3 and enter any number.

Now delete that number.


Gord Dibben MS Excel MVP
 
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.
 
Back
Top