changing cell values from within UDF

I made a UDF (so residing in a function module) and want to change the
value of a cell on "sheet1" but this doesn't work. I can read the value
but not set it. Like it was read-only. It doesn't seem to work with
other properties (e.g. colour) to

Regards
Benny

Benny,

A UDF can only return a result, it cannot change properties of a worksheet
or cells. The result will show in the cell that the UDF is called in but
that is it.

If you want to set colour, you could use conditional formatting based upon
the value in a cell.

--

HTH

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


<[email protected]> wrote in message
news:[email protected]...
> I made a UDF (so residing in a function module) and want to change the
> value of a cell on "sheet1" but this doesn't work. I can read the value
> but not set it. Like it was read-only. It doesn't seem to work with
> other properties (e.g. colour) to
>
> Regards
> Benny
>

A function called from a worksheet cell cannot make any changes
to the Excel environment, including change the value of another
cell. It can only return a value to the which called it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<[email protected]> wrote in message
news:[email protected]...
>I made a UDF (so residing in a function module) and want to
>change the
> value of a cell on "sheet1" but this doesn't work. I can read
> the value
> but not set it. Like it was read-only. It doesn't seem to work
> with
> other properties (e.g. colour) to
>
> Regards
> Benny
>

UDF's are not allowed to change the structure of the worksheet. You'll have
to put the changes you want to make in a SUB module.

<[email protected]> wrote in message
news:[email protected]...
> I made a UDF (so residing in a function module) and want to change the
> value of a cell on "sheet1" but this doesn't work. I can read the value
> but not set it. Like it was read-only. It doesn't seem to work with
> other properties (e.g. colour) to
>
> Regards
> Benny
>