Problem with setting Interior Color in VBA
 

Hello!

I have the following - strange - problem.

I want to set the interior color of a range object in VBA. So far so
good. I created a code and added it to the click event of a button. And
it works fine:

Worksheets("Sheet3").Range("R1").Interior.ColorInd ex = 17

But I want to have this code somewhere else. I wrote a class which is
called via a function, which is embedded in a cell. The function of the
cell is =fctXY(a;b;c) etc...

This function uses my object which calls the function that changes the
cell color. But nothing happens. The color doesn't change? I already
tried to turn the automatic cell calculation off, but the same result -
nothing happens. I also can not write a value into another cell when
I'm in the class function which is called by the formula in the cells

Can someone tell me why? Is there a lock for all the cells while the
formula is calculated? Can I turn this off?

Thank you for your help!

Bye,
Christoph

Problem with setting Interior Color in VBA
 

A function called from a worksheet formula cannot change the excel
enviroment e.g. make any changes to formatting. It can only return a
value to the cell.

Regards
Rowan

cneumann wrote:
Hello!

I have the following - strange - problem.

I want to set the interior color of a range object in VBA. So far so
good. I created a code and added it to the click event of a button. And
it works fine:

Worksheets("Sheet3").Range("R1").Interior.ColorInd ex = 17

But I want to have this code somewhere else. I wrote a class which is
called via a function, which is embedded in a cell. The function of the
cell is =fctXY(a;b;c) etc...

This function uses my object which calls the function that changes the
cell color. But nothing happens. The color doesn't change? I already
tried to turn the automatic cell calculation off, but the same result -
nothing happens. I also can not write a value into another cell when
I'm in the class function which is called by the formula in the cells

Can someone tell me why? Is there a lock for all the cells while the
formula is calculated? Can I turn this off?

Thank you for your help!

Bye,
Christoph

Problem with setting Interior Color in VBA
 

I am not sure how your code interacts, but you cannot change a cell
attribute from within a UDF, not even via a called class.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cneumann" wrote in message
ups.com...
Hello!

I have the following - strange - problem.

I want to set the interior color of a range object in VBA. So far so
good. I created a code and added it to the click event of a button. And
it works fine:

Worksheets("Sheet3").Range("R1").Interior.ColorInd ex = 17

But I want to have this code somewhere else. I wrote a class which is
called via a function, which is embedded in a cell. The function of the
cell is =fctXY(a;b;c) etc...

This function uses my object which calls the function that changes the
cell color. But nothing happens. The color doesn't change? I already
tried to turn the automatic cell calculation off, but the same result -
nothing happens. I also can not write a value into another cell when
I'm in the class function which is called by the formula in the cells

Can someone tell me why? Is there a lock for all the cells while the
formula is calculated? Can I turn this off?

Thank you for your help!

Bye,
Christoph