Can macros/functions interrupt formulas?
 

In column A is 20 cells set aside for user input. Column B contains
LOOKUP formulas and returns text based on Column A.

I had a string of commentText functions for A1:A20 that if the once the
user entered a value (whole number 1-17) they were prompted for a text
comment via a inputBox that was recorded in A140:A160 by
=commenttext(A1), etc.

I've figured out the functions didn't work like I wanted, so I deleted
them and the =commenttext() in A140:A160.

Now...when I go to enter a number into cells A1:A20, the LOOKUP
function in column B does not execute until I Save, or Print.

Ideas?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379322

Can macros/functions interrupt formulas?
 

Check your calculation setting (Tools - Options - Calculations Tab
-Automatic). If your code toggled that setting Application.Calculation =
xlManual and the code ended prematurelly then that would describe why it only
recalcs on save. Any time your code toggles an application setting it is a
good idea to use an errorhandler to reset the setting in the event of a
crash...
--
HTH...

Jim Thomlinson


"BigDave" wrote:


In column A is 20 cells set aside for user input. Column B contains
LOOKUP formulas and returns text based on Column A.

I had a string of commentText functions for A1:A20 that if the once the
user entered a value (whole number 1-17) they were prompted for a text
comment via a inputBox that was recorded in A140:A160 by
=commenttext(A1), etc.

I've figured out the functions didn't work like I wanted, so I deleted
them and the =commenttext() in A140:A160.

Now...when I go to enter a number into cells A1:A20, the LOOKUP
function in column B does not execute until I Save, or Print.

Ideas?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379322

Can macros/functions interrupt formulas?
 

Well, if Calculation is set to Manual it can...

Figured out my own problem. Thanks

--
BigDav
-----------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=37932

Can macros/functions interrupt formulas?
 

Can you elaborate on the errorhandler? Newb here in search of
lesson.

My code does set the calculation mode to manual (hence the origina
nature of this post) and I put the code back in to reset it

--
BigDav
-----------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=37932