Can't change any properties of range object from inside a function

  • Thread starter Thread starter dienkwik
  • Start date Start date
D

dienkwik

I wanted to have a function that can be invoked from one of the cells in
a worksheet that can change the color of the cell where the function is
invoked.

I inserted a new module in under the workbook and created a function to
do that.
The thing that frustrates me is that I seem to be able to read all the
properties of the objects that was passed in (in this case a range
object for the cell whose color I need to change), but I can't actually
modify any of them. I think it actually crashes, meaning the rest of
the code was not executed, and the cell where the function was invoked
displays "value!"

I have even tried changing the values directly in the debugger, but it
didn't take it either.

I then tried creating a macro and pasting the same code in and this
time it works fine.

I then thought maybe if I create a function that calls a macro that it
may work, but the same thing happened again, i.e. it didn't work. In
fact it actually stops the debugger. In the cell where the function is
invoked it says "value!"


Is there some kind of scope problem that I'm having, i.e. for some
reason I am not allowed to change properties of objects that I invoked
from the worksheets or something like that ?

Anybody has any idea what I should do ?

Thanks alot in advance.

Dien.
 
Hi
you can stop trying :-)
Functions are not able to change the Excel environment. Thats is they
can't change fonts, cell contents of other cells, etc. They can only
return values (and you're also able to insert comments)

so you may use a sub for doing this
 
Hi Dien

A function called from a cell can only do one single thing: return a value
to that one cell. It can not change other cells or otherwise modify its
environment. It's a choice of design by the ones that made Excel.

What you should do ? You provide little detail, but I believe Conditional
formatting should do what you want. Format menu.
 
Thanks for the replies.... I think the conditional formatting may solv
this problem without going into the trouble of scripting...:)

However, if for example I want to do things that are a little mor
complicted like changing the color of a cell based on the color o
another cell, or if I actually want to replace the value of one cel
with the value of another cell based on the content of some other cell
then can I still use conditional formatting ?

How does one invoke subs ? It seems like subs are not invoked from th
cells of worksheets, but rather it has to be run by executing macro
manually either by going to the tools menu or creating a button, etc.

Or perhaps I should rely on workbook events to trigger the checking an
running of subs ?

Thanks,

Dien
 
I played around a little with the conditional formatting and realize
that it can not use values in other worksheets for comparisons.

I was planning to have one summary sheets showing different colors fo
the cells depending on measurements calculated on other sheets.
One way to work around this is to bring all measurements from the othe
sheets onto the same worksheet so that I can use conditional formattin
with data on the same sheet.

Is there another way of doing what conditional formatting is doing bu
referencing values in other worksheets ?

(I'm basically trying to create a simple balanced scorecard strateg
map on one sheet and each individual measurement data and calculate
results in their respective sheets. The objectives on the strategy ma
would show green if the corresponding measurements are on track, an
red if they are not)

Thanks,

Dien
 
How does one invoke subs ? It seems like subs are not invoked from the
cells of worksheets, but rather it has to be run by executing macros
manually either by going to the tools menu or creating a button, etc.

Or perhaps I should rely on workbook events to trigger the checking and
running of subs ?
Click to expand...

Hi again Dien

Yes, there are lots of events that should monitor what happens and that can
call macros accordingly. See
http://www.cpearson.com/excel/events.htm

Simply make them start macros like something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target(1).Row = 5 then
If Target(1).Value = 300 then
Call FormatHardDriveMacro
End If
End IF
End Sub
 
Back
Top