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
Vyyk_DragoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vyyk_DragoAuthor Commented:
I've requested that this question be deleted for the following reason:

I resolved the problem myself.
0
Vyyk_DragoAuthor Commented:
Awesome, thanks!
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vyyk_DragoAuthor Commented:
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
Rgonzo1971Commented:
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
Vyyk_DragoAuthor Commented:
Excellent, thanks - that's very helpful!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.