Vyyk_Drago
asked on
TickLabels.Numberformat not working in Excel VBA
Hi
I'm working on some analytics tools built by another developer. In the spreadsheet, there are some charts linked to a combobox which is used to set the scaling (millions, billions, trillions) of the Y-Axis.
The combobox is then linked to a macro which scales the Y-axis based on the user selection and also applies a specific number format relevant to the scale in use.
However, for some reason it seems that the number format does not get accepted (no error is thrown, it is just ignored) or applied to the axis when a different scale is selected.
Even more curiously, if you query the number format of the Y-axis in the Immediate Window of the VBE after changing the selection of the scale, it actually returns the number format that it should have although it does not display it visually.
Furthermore, if you format the axis manually and apply the desired number format, then the correct format is displayed.
Is this a known issue or bug or is there some other subtle issue at play?
I attached a sample file which shows the problem: the blue cell displays the ordinal number which corresponds to the scale selected from the combobox. The orange cells constitute the source range of the combobox and the green cells are the cells that contain the numberformat that should be applied based on the scale applied.
The VBA code to format and scale the chart's Y-axis is linked to the combobox. (please see attached)
Thanks
Vyyk
Chart-Sample.xlsm
I'm working on some analytics tools built by another developer. In the spreadsheet, there are some charts linked to a combobox which is used to set the scaling (millions, billions, trillions) of the Y-Axis.
The combobox is then linked to a macro which scales the Y-axis based on the user selection and also applies a specific number format relevant to the scale in use.
However, for some reason it seems that the number format does not get accepted (no error is thrown, it is just ignored) or applied to the axis when a different scale is selected.
Even more curiously, if you query the number format of the Y-axis in the Immediate Window of the VBE after changing the selection of the scale, it actually returns the number format that it should have although it does not display it visually.
Furthermore, if you format the axis manually and apply the desired number format, then the correct format is displayed.
Is this a known issue or bug or is there some other subtle issue at play?
I attached a sample file which shows the problem: the blue cell displays the ordinal number which corresponds to the scale selected from the combobox. The orange cells constitute the source range of the combobox and the green cells are the cells that contain the numberformat that should be applied based on the scale applied.
The VBA code to format and scale the chart's Y-axis is linked to the combobox. (please see attached)
Thanks
Vyyk
Chart-Sample.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome, thanks!
ASKER
Moderator
Can you please keep this open and not delete?
For some reasons the answer from Rgonzo1971 did not show up until after I had requested to close the question.
All points should be awarded to Rgonzo1971.
Thanks
Vyyk
Can you please keep this open and not delete?
For some reasons the answer from Rgonzo1971 did not show up until after I had requested to close the question.
All points should be awarded to Rgonzo1971.
Thanks
Vyyk
Or if you want to use NumberFormat instead of NumberFormatLocal
use
Millions #\,##0_);(#\,##0)
Billions #\,##0.0_);(#\,##0.0)
Trillions #\,##0.00_);(#\,##0.00)
Regards
use
Millions #\,##0_);(#\,##0)
Billions #\,##0.0_);(#\,##0.0)
Trillions #\,##0.00_);(#\,##0.00)
Regards
ASKER
Excellent, thanks - that's very helpful!
ASKER
I resolved the problem myself.