?
Solved

TickLabels.Numberformat not working in Excel VBA

Posted on 2014-02-26
6
Medium Priority
?
2,171 Views
Last Modified: 2014-02-26
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
0
Comment
Question by:Vyyk_Drago
  • 4
  • 2
6 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39888784
Hi,$

pls try

Sub ScaleChartYAxis()

    Dim chrt As Chart
    
    Set chrt = ActiveSheet.ChartObjects("Chart 1").Chart

    With chrt.Axes(xlValue)
    a = .TickLabels.NumberFormat
        Select Case Range("L1").Value
            Case 1
                .DisplayUnit = xlMillions
            Case 2
                .DisplayUnit = xlThousandMillions
            Case 3
                .DisplayUnit = xlMillionMillions
        End Select
        .TickLabels.NumberFormatLocal = Range("N1").Offset(Range("L1").Value).Value
        Debug.Print .TickLabels.NumberFormat
    End With

End Sub

Open in new window

Regards
0
 

Author Comment

by:Vyyk_Drago
ID: 39888793
I've requested that this question be deleted for the following reason:

I resolved the problem myself.
0
 

Author Closing Comment

by:Vyyk_Drago
ID: 39888794
Awesome, thanks!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:Vyyk_Drago
ID: 39888799
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
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39888804
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
0
 

Author Comment

by:Vyyk_Drago
ID: 39888841
Excellent, thanks - that's very helpful!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question