Changing Font with Function

[Dan McCollick]

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)
'************************************************************************************************************
'Authoraniel 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

 

[Tom Ogilvy]

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.

 

[Chip Pearson]

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

 

[Guest]

WorkAround

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

 

[Dan McCollick]

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.

 

[Guest]

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...