Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    We have some charts, which they call Whale diagrams. Some of the charts have over 200 data points for the column chart, therefore they usually filter the data. The charts should be divided into 5 quintiles using gridlines. We are using "Number of categories between tick marks" to get the 5 quintiles (X axis gridlines).

    The question concerns creating a formula to get the number of tick marks between gridlines and putting that formula in the axis scale. Is it possible to put a formula into the Number of categories between tick marks cell?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't enter a cell reference or a formula in the axis properties.

    As an alternative, you could create a macro that updates the TickMarkSpacing property of the axis. If you'd like help with that, please provide detailed information and if possible, attach a sample workbook.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Hans,

    I'm not very good at VBA, but hopefully you can help.

    Attached is a sample workbook. We would filter by State or CL Name to make the chart easier to read. We always want the chart to have 5 divisions using the X axis gridlines.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use this macro:

    Code:
    Sub SetGridlineSpacing()
      Dim cht As Chart
      Dim axs As Axis
      Dim ser As Series
      Dim n As Long
      Set cht = ActiveSheet.ChartObjects(1).Chart
      Set ser = cht.SeriesCollection(1)
      n = ser.Points.Count
      Set axs = cht.Axes(xlCategory)
      axs.HasMajorGridlines = True
      axs.TickMarkSpacing = n / 5
    End Sub
    If you want the gridlines to be adjusted automatically, you can call the macro from the Worksheet_Calculate event in the worksheet module:

    Code:
    Private Sub Worksheet_Calculate()
      Call SetGridlineSpacing
    End Sub
    See the attached version: [attachment=86210:Whale_Sample.xls]

    Warning: if you filter the data such that the number of categories is not divisible by 5, the gridlines won't be exactly at quintiles; this will be particularly noticeable if the number of categories is small.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for the VBA code, Hans.

    I copied the modules from the attached file into the original workbook. I ran the macro for the graph unfiltered. It seems to divide the chart as needed, but when I use the auto filter buttons to select part of the list, the dividing lines either disappear or only one/two display. Did I miss something on the setup of the VBA?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code

    Code:
    Private Sub Worksheet_Calculate()
      Call SetGridlineSpacing
    End Sub
    should not be copied into a standard module, but in the module associated with the worksheet containing the data and chart. You can open this module by right-clicking the sheet tab and selecting View Code from the popup menu.

    The code assumes that
    1) the data and chart are in the same worksheet, and
    2) the chart is the only (or at least the first) chart on the worksheet

    If that is not true, the code will need to be modified.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Got it!

    Thanks again, Hans. I do see that the smaller selections do have issues with the breakdown, but we can modify manually if needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •