Changing Font with Function

Hello all. I have a user defined function that I am trying to get to
change the font of the cell that calls the function. Any idea why
ActiveCell.Font.Name="fontname" would not work. I have tried
Application.Caller.ActiveCell.Font.Name as well with no luck. Here is
the current code.

Function eavsplanindicator(ea, plan)
'************************************************************************************************************
'Author:Daniel McCollick
'Date: 5/13/05
'Purpose: To print a circle, square, or diamond as an indicator
depending on ea vs plan values
'************************************************************************************************************
R = Application.Caller.Row
C = Application.Caller.Column

If (ea <= plan) Then
ActiveCell.Font.Name = "Webdings 2"
'This does not change the font???

eavsplanindicator = "-"


End If
..
..
..
..


End Function

Functions used in a worksheet (UDF) can not change the excel environment -
they can only return a value to the cell in which they are located.

--
Regards,
Tom Ogilvy


"Dan McCollick" <[email protected]> wrote in message
news:[email protected]...
> Hello all. I have a user defined function that I am trying to get to
> change the font of the cell that calls the function. Any idea why
> ActiveCell.Font.Name="fontname" would not work. I have tried
> Application.Caller.ActiveCell.Font.Name as well with no luck. Here is
> the current code.
>
> Function eavsplanindicator(ea, plan)
>
'***************************************************************************
*********************************
> 'Author:Daniel McCollick
> 'Date: 5/13/05
> 'Purpose: To print a circle, square, or diamond as an indicator
> depending on ea vs plan values
>
'***************************************************************************
*********************************
> R = Application.Caller.Row
> C = Application.Caller.Column
>
> If (ea <= plan) Then
> ActiveCell.Font.Name = "Webdings 2"
> 'This does not change the font???
>
> eavsplanindicator = "-"
>
>
> End If
> .
> .
> .
> .
>
>
> End Function
>

Dan,

A function called from a worksheet cell cannot change the font of
a cell. Indeed, it cannot change anything at all. The only this
it can do is return a value to the cell calling it.


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




"Dan McCollick" <[email protected]> wrote in message
news:[email protected]...
> Hello all. I have a user defined function that I am trying to
> get to
> change the font of the cell that calls the function. Any idea
> why
> ActiveCell.Font.Name="fontname" would not work. I have tried
> Application.Caller.ActiveCell.Font.Name as well with no luck.
> Here is
> the current code.
>
> Function eavsplanindicator(ea, plan)
> '************************************************************************************************************
> 'Author:Daniel McCollick
> 'Date: 5/13/05
> 'Purpose: To print a circle, square, or diamond as an
> indicator
> depending on ea vs plan values
> '************************************************************************************************************
> R = Application.Caller.Row
> C = Application.Caller.Column
>
> If (ea <= plan) Then
> ActiveCell.Font.Name = "Webdings 2"
> 'This does not change the font???
>
> eavsplanindicator = "-"
>
>
> End If
> .
> .
> .
> .
>
>
> End Function
>

WorkAround

Use Conditional Formatting to set font based on the results of the
function....

"Dan McCollick" wrote:

> Hello all. I have a user defined function that I am trying to get to
> change the font of the cell that calls the function. Any idea why
> ActiveCell.Font.Name="fontname" would not work. I have tried
> Application.Caller.ActiveCell.Font.Name as well with no luck. Here is
> the current code.
>
> Function eavsplanindicator(ea, plan)
> '************************************************************************************************************
> 'Author:Daniel McCollick
> 'Date: 5/13/05
> 'Purpose: To print a circle, square, or diamond as an indicator
> depending on ea vs plan values
> '************************************************************************************************************
> R = Application.Caller.Row
> C = Application.Caller.Column
>
> If (ea <= plan) Then
> ActiveCell.Font.Name = "Webdings 2"
> 'This does not change the font???
>
> eavsplanindicator = "-"
>
>
> End If
> ..
> ..
> ..
> ..
>
>
> End Function
>
>

This would still require you to edit the worksheet on a per worksheet
basis. My goal was to have an add in where the user could simply
install the add in, type the equation in the formula bar, and then not
have to worry about formatting. Any other suggestions?
Thanks again for the comments so far.

OK, back to VBA

how versitile does this need to be?
any cell?
any cell in 1 column?
any workbook?


give more specific on desired usage now that we have established that it
will require code beyond a sheet function.

launch could be from a for, a menu bar item...
"Dan McCollick" wrote:

> This would still require you to edit the worksheet on a per worksheet
> basis. My goal was to have an add in where the user could simply
> install the add in, type the equation in the formula bar, and then not
> have to worry about formatting. Any other suggestions?
> Thanks again for the comments so far.
>
>