Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with setting Interior Color in VBA
Functions return values to their calling cells - they can't affect other
cells or the environment, including color. Abstracting the change to a function called by another function doesn't change this. You can work around this with event macros - either the Worksheet_Calculate or the Worksheet_Change events are the most common. In article .com, "cneumann" wrote: Can someone tell me why? Is there a lock for all the cells while the formula is calculated? Can I turn this off? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with setting Interior Color in VBA
You probably need to use a macro rather than a function.
-- Gary's Student "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with setting Interior Color in VBA
Hi!
Thanks for your replies. I dont want to change other cells just the one which calls the function. The problem with the other events is, that I have to do a lot more work in order to find the related cell etc. (due to the other sheets and the requirements around). I need to change the color of the sheet based on the value of the cell. Similar to the conditional formatting in Excel, but far more complicated so that I can't use this feature. It should be possible to do the same like this feature as it changes the cell colors also depending on the input value! If you have any futher ideas - please let me know! Thanks Christoph |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with setting Interior Color in VBA
As far as changing formatting, it doesn't matter which cell your talking
about - XL functions can't change the formatting in the calling cell, either. Events are the only way... However, you mention changing cell colors based on input value - that's certainly available via Format/Conditional Formatting... In article .com, "cneumann" wrote: Hi! Thanks for your replies. I dont want to change other cells just the one which calls the function. The problem with the other events is, that I have to do a lot more work in order to find the related cell etc. (due to the other sheets and the requirements around). I need to change the color of the sheet based on the value of the cell. Similar to the conditional formatting in Excel, but far more complicated so that I can't use this feature. It should be possible to do the same like this feature as it changes the cell colors also depending on the input value! If you have any futher ideas - please let me know! Thanks Christoph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with setting Interior Color in VBA | Excel Programming | |||
interior color of a cell | Excel Programming | |||
Passing Back Color to Interior Color | Excel Programming | |||
Problem in setting Cell Interior Color filling. | Excel Programming | |||
Interior Cell color | Excel Programming |