Dear Excel Genius guys

i have a small query
can there be Numberformat in UDF (User Defined Function)

My VBA Code is

Function Crore (Selcell as variant)
crore=selcell/10000000
end function

now i want that at the same time its number format should also changed
& show us
"#,#00.000[$ Cr.]" 2 Cr.

now for that i have macro

sub crnumberformat()
ActiveCell.NumberFormat = "#,#00.000[$ Cr.]"
end sub

my query is how to call a macro in function

Anand

You will need to re-think this for two reasons

1) You can only return a value from a function and this is not formatted
2) You would be calling a sub BEFORE any value was returned (even if it
would work)

You might look at doing your formatting using a Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents=False
Target.NumberFormat = "#,#00.000[$ Cr.]"
Application.EnableEvents=True
End Sub

You would also be able to restrict this, for example to column A by the
following

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Columns("A"), Target) Is Nothing Then
Application.EnableEvents = False
Target.NumberFormat = "#,#00.000[$ Cr.]"
Application.EnableEvents = True
End If
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(E-Mail Removed)DTHIS
www.nickhodge.co.uk


"Anand the Smarty" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dear Excel Genius guys
>
> i have a small query
> can there be Numberformat in UDF (User Defined Function)
>
> My VBA Code is
>
> Function Crore (Selcell as variant)
> crore=selcell/10000000
> end function
>
> now i want that at the same time its number format should also changed
> & show us
> "#,#00.000[$ Cr.]" 2 Cr.
>
> now for that i have macro
>
> sub crnumberformat()
> ActiveCell.NumberFormat = "#,#00.000[$ Cr.]"
> end sub
>
> my query is how to call a macro in function
>