Numberformat in ADDIN module doesn't work.
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 |
Numberformat in ADDIN module doesn't work.
Hi
what exactly happens with your active cell? -- Regards Frank Kabel Frankfurt, Germany "X.Yu" schrieb im Newsbeitrag ... 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 |
Numberformat in ADDIN module doesn't work.
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. "Frank Kabel" wrote: Hi what exactly happens with your active cell? -- Regards Frank Kabel Frankfurt, Germany "X.Yu" schrieb im Newsbeitrag ... 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 |
Numberformat in ADDIN module doesn't work.
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() -- Regards, Tom Ogilvy "X.Yu" wrote in message ... 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 |
Numberformat in ADDIN module doesn't work.
Thanks Tom.
Dropdown_Test() works fine. That's the reason I put function here to prove some setting can be changed. "Tom Ogilvy" wrote: 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() -- Regards, Tom Ogilvy "X.Yu" wrote in message ... 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 |
Numberformat in ADDIN module doesn't work.
So alter Numberformat_pc_test to put data validation in the cell. That is
what you proved worked. Formatting doesn't as I stated. -- Regards, Tom Ogilvy "X.Yu" wrote in message ... Thanks Tom. Dropdown_Test() works fine. That's the reason I put function here to prove some setting can be changed. "Tom Ogilvy" wrote: 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() -- Regards, Tom Ogilvy "X.Yu" wrote in message ... 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 |
All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com