Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Automatic update of Function return value.

  Asked By: Everett    Date: Oct 06    Category: MS Office    Views: 814
  

Did find any old posts on this topic so here we go:

I have a written a small function that returns Interior.ColorIndex.
Works well except that then when I change the color of the range the
function doesn't update automatically. I need to click in function for
it to return new value.

Help appreciated!!

Function to be used in conjunction with Autofilter so sales staff
indicating status of opportunity by color code easily can sort on it.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Eddie Austin     Answered On: Oct 06

I don't know how to do what you want, but <Ctrl><Alt><F9> will update
it.

I believe that with Excel (from Excel 2000 onwards), this can be done
in VBA with the command
Application.CalculateFull

 
Answer #2    Answered By: Antonio Dunn     Answered On: Oct 06

I tried Application.Calculate and the code runs without error but
doesn't update  the function. With Application.CalculateFull the
function returns  Value error.

Was told that I need to use class modules but I don't know what it is
so I need to read up on it to figure out a way.

 
Answer #3    Answered By: Holly Brown     Answered On: Oct 06

Another option would be to have a macro which replaces all incidences
of an equals sign with an equals sign using something like:

Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart

This forces each cell with a function  to be changed and then
calculated.
You'll need to decide if you want to do all the sheets in the
workbook.

 
Didn't find what you were looking for? Find more on Automatic update of Function return value. Or get search suggestion and latest updates.




Tagged: