Hi Dave,

Thanks for your kind reply.

Only one thing, how can build up a add in which can get this function work
properly.

My basic requirement is call the function from "Insert Function", where to
call function in an addin xla file.

Thanks in adbvance.


"Dave Peterson" wrote:

My real question is how is this code run?

Do you put:
=numberformat_pc_test()
in a cell on a worksheet

If you do, then you can't change the number format--you can only return the
value.

If you call it from a Sub...

like this:

Option Explicit
Sub testme()
ActiveCell.Value = numberformat_pc_test
End Sub
Function numberformat_pc_test()
ActiveCell.NumberFormat = "0.00"
numberformat_pc_test = 0.234
End Function

It worked ok for me.

Although, I think I would have used something like:

Option Explicit
Sub testme()
ActiveCell.Value = numberformat_pc_test(ActiveCell)
End Sub
Function numberformat_pc_test(rng As Range) As Variant
rng.NumberFormat = "0.00"
numberformat_pc_test = 0.234
End Function

But I'm guessing you're trying to do something that excel won't allow (the first
portion).

X.Yu wrote:

Thanks Dave for your kind reply. Here is my further explaination.

Following two functions are used to test change cell value and format. the
dropdown_test function changes the cell setting to dropdown, which works. The
second function tries to change the number format, and it doesn't work.

Both functions locate in module in a test.xla file and called from Excel
User Defined category.

Function dropdown_test()
ActiveCell.Select
ActiveCell.Clear
With Selection.Validation
.Delete
.Add Type:=xlValidateList, Operator:=xlBetween,
Formula1:="a,b,c,d,e"
.IgnoreBlank = True
.InCellDropdown = True
End With
dropdown_test = "a"
End Function

Function numberformat_pc_test()
ActiveCell.NumberFormat = "0.00"
numberformat_pc_test = 0.234
End Function

"Dave Peterson" wrote:

How are you calling this function?

If you're calling it from a worksheet formula, then you can only return
values--you can't change formats (or change other cells.



X.Yu wrote:

this function is in a module in test.xla

Function numberformat_pc_test()
ActiveCell.NumberFormat = "0.00"
numberformat_pc_test = 0.234
End Function

"X.Yu" wrote:

I try to change the cell number format in my "add in" module using following
statement:

ActiveCell.NumberFormat = "0.00%"

But it does not change the the format, the cell number format was still
"General".

Any suggestion?

--

Dave Peterson



--

Dave Peterson