Prevent the edit of a cell backcolor from call the associated function ?

Excel:  I want to be able to edit the contents of a cell and not have it call the function that the cell contains.
The following function creates an error "Circular reference ..."

// Cell Contents:
  = MyProc(A1 - A2, A3)

// Code from module
function  MyProc(val as long, cell as variant) as string

    if val < 0 then
         MySheet.Cells(cell).Locked = True                                          // <--  this doesn't help
         MySheet.Cells(cell).Interior.Color = vbRed
         MySheet.Cells(cell).Locked = False
    end if

    MyProc = cstr(val)
end function
sidwelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
You can't use a UDF to change the format, e.g. Interior, of a cell.
0
sidwelleAuthor Commented:
Yes, but it also updates the cell and fires the function again.
Circular reference ...
0
NorieVBA ExpertCommented:
What exactly are you trying to do with this function?

Where are you using?

If you are using it in a cell on a worksheet you can't use it to do anything other than return a value to the cell it's being used in.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sidwelleAuthor Commented:
Exactly, I want to return a value, but I also want to set some attributes on the cell.

There is no way to prevent the cells from calling the function again ?
some way to disable the auto-update ?
0
NorieVBA ExpertCommented:
You cannot set the attributes, e.g. formatting, of a cell using a UDF.
0
sidwelleAuthor Commented:
I do it all the time, just is the first time I tried from a function called by the cell.
0
NorieVBA ExpertCommented:
Can you post an example?
0
sidwelleAuthor Commented:
I did in my initial question:

// Code from module
 function  MyProc(val as long, cell as variant) as string
          MySheet.Cells(cell).Interior.Color = vbRed       // <-- here ...
 end function 

Open in new window

0
NorieVBA ExpertCommented:
How would you use the function in a cell?
0
sidwelleAuthor Commented:
=MyProc(A1, A1)
0
NorieVBA ExpertCommented:
If you used that in a cell it would return #VALUE! to the cell the formula was in and no formatting would be applied.
0
sidwelleAuthor Commented:
You ask how to set the cell properties ?

// Code from module
 function  MyProc(val as long, cell as variant) as string
          MySheet.Cells(cell).Interior.Color = vbRed       // <-- here ...
          MyProc = cstr(val)          // <-- fixed 
 end function 

Open in new window

0
NorieVBA ExpertCommented:
Again, if used on a worksheet that formula will result in #VALUE! returned to the cell it's placed in and no formatting applied.
0
sidwelleAuthor Commented:
Yes, it does this because as soon as you access or edit any of the cells attributes or value,  the code immediately exits the proc.
Microsoft must have left some feature behind disable the call back to the cell to allow you to continue w/u'r procedure ?

That's what I am looking.

Place a break-point on the following line and what the compiler exit the proceedure:
MySheet.Cells(cell).Interior.Color = vbRed
0
NorieVBA ExpertCommented:
I'm sorry but I'm not following.

You cannot use a UDF to apply formatting.

What is it you exactly asking/wanting to do?

PS I know the procedure will exit on that line as it will on any line in a UDF that causes an error.
0
sidwelleAuthor Commented:
Are you stating that you can update the format from a function ?
Run the function from a button, it works fine !
0
NorieVBA ExpertCommented:
No I'm stating that you cannot use a function in a cell to apply formatting.
0
sidwelleAuthor Commented:
Yes, because it calls for an update to the cell.
Is there a way to prevent the cell from making this call ?
0
NorieVBA ExpertCommented:
Remove the code that tries to do the update?
0
sidwelleAuthor Commented:
That's the code that I want to keep !
0
NorieVBA ExpertCommented:
I'm sorry but you can't keep that code.

As I've being saying all through this you cannot use a UDF to apply formatting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sidwelleAuthor Commented:
I don't have time to pursue this anymore, but I am disappointed the M$ didn't leave you a method to update the format w/o invoking a cell-update.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.