Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with setting Interior Color in VBA cneumann Excel Programming 2 December 13th 05 11:56 PM
interior color of a cell R.VENKATARAMAN Excel Programming 9 November 11th 05 05:15 AM
Passing Back Color to Interior Color ExcelMonkey[_190_] Excel Programming 1 March 22nd 05 05:27 PM
Problem in setting Cell Interior Color filling. Venkatachalam Excel Programming 1 September 29th 04 07:13 PM
Interior Cell color Pellechi Excel Programming 1 September 23rd 03 03:39 PM


All times are GMT +1. The time now is 03:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"