Automatically control the font of a cell when a function is used

[Subodh]

I have a my custom function say myfun(arguments)
I would like that whenever I use this function in any cell of a
workbook (sheet)
there should be always be the same font lets say Times New Roman, the
font size to be 10, font color automatic.
I tried to make another sub that will check every time the sub is
entered and ensure that the cell is well
with the desired font type. But, I could not get through the sub.
Any help in this regard will be appreciated.
Thanks in advance.

 

[OssieMac]

I don't think that you can make changes to the worksheet from within the UDF
but you could use a Worksheet_Change event. It will only set the font when
you enter or change the formula and there is nothing to prevent the user
changing the format of the cell later.

Not sure of your expertise with VBA but just in case you need help. Right
click the worksheet tab and select View code and the VBA editor will open at
the worksheets code. Copy the code and paste into the worksheets code area.

Private Sub Worksheet_Change(ByVal Target As Range)
If (Left(LCase(Target.Formula), 6)) = "=myfun" Then
Target.Font.Bold = True
Target.Font.ColorIndex = 3 'Red font
End If
End Sub

 

[Subodh]

I don't think that you can make changes to the worksheet from within the UDF
but you could use a Worksheet_Change event. It will only set the font when
you enter or change the formula and there is nothing to prevent the user
changing the format of the cell later.

Not sure of your expertise with VBA but just in case you need help. Right
click the worksheet tab and select View code and the VBA editor will openat
the worksheets code. Copy the code and paste into the worksheets code area.

Private Sub Worksheet_Change(ByVal Target As Range)
  If (Left(LCase(Target.Formula), 6)) = "=myfun" Then
    Target.Font.Bold = True
    Target.Font.ColorIndex = 3   'Red font
  End If
End Sub

--
Regards,

OssieMac





- Show quoted text -

Thanks