Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
Hi
works for me without a problem. what is your complete code? -- Regards Frank Kabel Frankfurt, Germany "X.Yu" schrieb im Newsbeitrag ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can not change the numberformat
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.numberformat Q | Excel Worksheet Functions | |||
NumberFormat | Excel Discussion (Misc queries) | |||
numberformat? | Excel Discussion (Misc queries) | |||
NumberFormat? | Excel Worksheet Functions | |||
Numberformat | Excel Programming |