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