Consulting

Results 1 to 8 of 8

Thread: VBA control chart Y-Axis

  1. #1

    VBA control chart Y-Axis

    Hi good people!,

    I have this code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet.ChartObjects("Chart 5").Chart
        Select Case Target.Address
          Case "$A$2"
            .Axes(xlValue).MaximumScale = Target.Value
          Case "$A$3"
            .Axes(xlValue).MinimumScale = Target.Value
          Case "$A$4"
            .Axes(xlValue).MajorUnit = Target.Value
        End Select
      End With
    End Sub
    which I got from, I think Mark Peltier's site, but just cannot get it to work. I have a dropdown which selects the criteria I want to view on the chart. The Chart itself works fine, I just need to have the Y-axis scale according to what I am viewing. Please, if someone could shed some light on this for me?

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    It's Jon Peltier, not Mark.

    Please define "cannot get it to work" - what happens?
    Be as you wish to seem

  3. #3
    Oops, apology for getting that wrong.

    Well, Nothing happens, the chart chooses it's own range as I go along. I have a scroll bar which clicks through a date range, and every date range obviously has different values, so every click changes the chart and also the Y-axis values. What I have is in A2 there is an IF formula, which changes the MAX I want, depending on what I'm viewing, In A3 I have a zero (MIN value), and in A4 another IF formula which sorts out the Major unit value. But all this is ignored, so to speak..

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    Formulas don't trigger the change event. You could simply assign a macro to the scrollbar to update the axis. To use the Change event you'd need to monitor the input cell, not the formula cell, or you could use the sheet's Calculate event instead. You could even trap the calculate event of the chart itself if you want.
    Be as you wish to seem

  5. #5
    I have also tried this now..no joy..Please would you help me?

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$O$1" Then
    If Range("O1") = "Handle" Then
    With ActiveSheet.ChartObjects("Chart 5").Chart
            .Axes(xlValue).MaximumScale = 100000
            .Axes(xlValue).MinimumScale = 0
            .Axes(xlValue).MajorUnit = 20000
       End With
    End If
    If Range("O1") = "Games" Then
    With ActiveSheet.ChartObjects("Chart 5").Chart
            .Axes(xlValue).MaximumScale = 5000
            .Axes(xlValue).MinimumScale = 0
            .Axes(xlValue).MajorUnit = 500
       End With
    End If
    End If
    End Sub
    Also, the code in post #1 works if I type the values in manually, but not if they change because of formula?

  6. #6
    Yep, well, thanx for the help...I'll see if I can come right with a bit of googling. Thanx Aflatoon..

  7. #7
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    You've already got the basic code...
    Be as you wish to seem

  8. #8
    That's right, should be fine soon..thanx..

Posting Permissions

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