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

sidwelle
sidwelle used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
You can't use a UDF to change the format, e.g. Interior, of a cell.

Author

Commented:
Yes, but it also updates the cell and fires the function again.
Circular reference ...
NorieAnalyst Assistant

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 ?
NorieAnalyst Assistant

Commented:
You cannot set the attributes, e.g. formatting, of a cell using a UDF.

Author

Commented:
I do it all the time, just is the first time I tried from a function called by the cell.
NorieAnalyst Assistant

Commented:
Can you post an example?

Author

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

NorieAnalyst Assistant

Commented:
How would you use the function in a cell?

Author

Commented:
=MyProc(A1, A1)
NorieAnalyst Assistant

Commented:
If you used that in a cell it would return #VALUE! to the cell the formula was in and no formatting would be applied.

Author

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

NorieAnalyst Assistant

Commented:
Again, if used on a worksheet that formula will result in #VALUE! returned to the cell it's placed in and no formatting applied.

Author

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
NorieAnalyst Assistant

Commented:
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.

Author

Commented:
Are you stating that you can update the format from a function ?
Run the function from a button, it works fine !
NorieAnalyst Assistant

Commented:
No I'm stating that you cannot use a function in a cell to apply formatting.

Author

Commented:
Yes, because it calls for an update to the cell.
Is there a way to prevent the cell from making this call ?
NorieAnalyst Assistant

Commented:
Remove the code that tries to do the update?

Author

Commented:
That's the code that I want to keep !
Analyst Assistant
Commented:
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.

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial