formula/value coexistence

[Sergio deRada]

Is there a way to keep a formula in a cell when a value
is typed into it? In other words, I am trying to do
something like this: (IF A4 < 0 , B4 = whateverthe user
types in B4, B4 = B4*A4)

There has to be a way to be able to keep formulas
and "typed contents" to coexist

Help!

[Hank Scorpio]

On Sat, 22 Feb 2003 22:58:05 -0800, "Sergio deRada" <sde...@aol.com>
wrote:

I'm assuming that you want that formula in B4. The short answer is no,
you can't do that. The only instance I've seen of something like that
is through an add-in for an OLAP product called TM1. TM1 has DBR() or
DBRW() formulas which allow the user to punch in a number, and the
formula stays in tact while sending the value that was typed in to the
TM1 cube. (The price that is paid for this is that it COMPLETELY
screws up the worksheet change event, making it impossible to use
those types of macros with TM1.) However this is done by way of a
MAJOR chunk of software operating in the background.

You therefore have two options:
1/ Have the data input somewhere OTHER than B4, and have B4 just
return the value; OR
2/ Use the worksheet change event to assign the appropriate value to
B4. To do that, right click on the sheet tab, select View Code, and
paste this into the code sheet that appears:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$4" Then

If IsNumeric(Range("A4")) And IsNumeric(Range("B4")) Then

If Range("A4") < 0 Then

'Stop this procedure from being triggered infinitely.
Application.EnableEvents = False

Range("B4") = Range("B4") * Range("A4")

Application.EnableEvents = True

End If

End If

End If

End Sub

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *