Setting a Range object's value overwrites its formula?

[Goh, Yong Kwang]

Dear all,

I currently have a worksheet that has some cells for user's input and
some with formulas embedded.

To make things easier for the user to do some analysis, I created a
dialog box with some textboxes in Excel VBA.

Basically, I've some lines that resembles:

txtPlantCurrent.Text = ThisWorkbook.Worksheets("ERP Production
Allocation").Range("B2").Value

to load in the value from the worksheet and intialize the textbox in
the dialog box which works fine.

however, on clicking the OK button in the dialog box, the changed or
updated value should be set in the worksheet cell for calculation.

So I've some lines that looks like this for the OK button event
handler:

ThisWorkbook.Worksheets("ERP Production Allocation").Range("B2").Value
= CDbl(txtPlantCurrent.Text)

To my horror, this changing of value in the B2 cell actually
overwrites its formula: "=B12*B7+B13*C7+B11*D7" and the other cells
that depend on this changed cell doesn't work anymore.

What am I doing wrong here?

Goh, Yong Kwang
Singapore
(e-mail address removed)

 

[JE McGimpsey]

You're not doing anything wrong, per se, but a cell can either contain a
user-entered value or it can contain a formula, but not both.

You need to decide whether B2 should be a calculated result or whether
it should be a user-entered value.