Over-written formulas

[dcornett63]

I, along with some help from others who know a lot more about Excel than I
do, have created a batch of forms using Excel and VB code. We have a bug that
none of us can figure out.

There is a formula in a cell that takes the value of another cell on a
different tab (a dollar amount) and raises that amount to the next $500
{=IF('Cost Est'!G76=0,0,'Cost Est'!G76-MOD('Cost Est'!G76,500)+500}. The
problem is, sometimes that formula is over-written with a value. Therefore,
if changes are made to the “Cost Est†sheet, the changes are not reflected in
the cell. This doesn’t happen every time and we can’t seem to figure out what
is making it happen.

We don’t believe that this is occurring because of any code because it has
happened even when the code is disabled. We have searched the sheet and the
code to see if we can find anything that might over-write a cell’s formula
and have been unsuccessful.

If anyone has any ideas, please let me know. I’d be glad to send you the
forms if that will help. (There is nothing confidential involved.) Also,
please note that we are using a very old version (2000) of Microsoft. What
can I say, we’re a State government agency and a little behind the times.

 

[Max]

If anyone has any ideas, please let me know ..

One thought: impose a control, then monitor closely whether it still recurs.
Password protect the formulas on that sheet, with the password strictly
restricted to a single person/his cover. If it doesn't recur after that,
you've isolated the cause - human error. Any good? hit the YES below,
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[dcornett63]

Max,
thanks for the response.

Actually, this is occurring even in the testing stages while I am testing my
code. So, I know that it isn't someone going in and overriding my formulas.

However, I may have (thanks to your response) figured out the problem. It
may have something to do with data valadation. I had been using data
valadation to restrict the user to decimals (to restrict them from entering
text into this field) and to let the user know that the cell will
self-populate from another tab but can be overwritten if necessary. I have
now removed the data valadation and am using a comment to notify them.

It seems to be working at the moment, but as I stated in the original
message, it doesn't occur every time so I can't really be sure that the
problem is fixed or if it's just not happening right now. Guess we'll see.
Thanks again for the help