Pivotchart changing category(x) axis scale

Posted on 2006-04-27
Last Modified: 2008-01-09

I've created a pivotchart and I need to change the lables spacing and Tick mark spacing based upon the length of time the user has selected.  I.E. if the user wants to show data for 12 months then the lables spacing is 1 and the tick mark spacing is 1, if the user wants to show 36 months then the spacings change to 3 each, etc.

I don't know how to change theses settings in VBA.  And where would be the best place to put the code?  The pivotchart form is a subform of a report.

Thank you in advance.

Miss Mimi
Question by:Miss_Mimi
    LVL 119

    Expert Comment

    by:Rey Obrero
    this might help

    How to Programmatically Build a PivotChart View in an Access Form

    Author Comment

    Yeah I actually went through that exercise and it doesn't change the scale settings.

    I know it can be done manually, because I change the settings when I was testing the PivotChart.

    LVL 119

    Accepted Solution

    from the example this will change the value of the y axis

      With objChartSpace.Charts(0).Axes(1)
            .MajorUnit = 200
            .MinorUnit = 20      
      End With

    ' this will change the value of units in the x axis

      With objChartSpace.Charts(0).Axes(0)
            .MajorUnit = 10
            .MinorUnit = 1      
      End With

    just play with the values to get what you want


    Author Comment

    That worked.  Thank you.
    LVL 119

    Expert Comment

    by:Rey Obrero
    U R Welcome!!!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now