Numberformat in ADDIN module doesn't work.

[Guest]

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 expect a
percentage format present. It doesn't work. Hope someone can tell me the
reason why 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

 

[Frank Kabel]

Hi
what exactly happens with your active cell?

 

[Guest]

Thanks Frank.

The active cell number fomat does not change. Always "General".

I expect percentage data come out instead of decimal number, but it still
decimal data come out.

In debug mode, I try change the format in runtime, but it doesn't change.

 

[Tom Ogilvy]

If, in a cell you do

=numberformat_pc_test()

Then functions called in cells (user defined functions) are not allowed to
change the environment such as formatting and so forth. They can only
return a value to the cell in which they are used/called.


I assume you are not doing

=DropDown_test()

 

[Guest]

Thanks Tom.

Dropdown_Test() works fine. That's the reason I put function here to prove
some setting can be changed.

 

[Tom Ogilvy]

So alter Numberformat_pc_test to put data validation in the cell. That is
what you proved worked. Formatting doesn't as I stated.