+ Reply to Thread
Results 1 to 2 of 2

Changing secondary axes with VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    10-21-2003
    Posts
    2

    Changing secondary axes with VBA

    I found the following code to change the scale of the primary x and y axes of a chart by changing cells in a worksheet. The existing chart has a secondary axis that I would like to be able to also change by changing a cell value as well. When I copied the code and modified it so that the AxisGroup specified the seconadry axis, the code doesn't work. The code is in the worksheet module page and not a stand alone module. I can not figure out what is going on. Any help will be very appreciated. I will be glad to provide more detail is needed. Here is the code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$L$60"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
                .MaximumScale = Target.Value
        Case "$L$61"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
                .MinimumScale = Target.Value
        Case "$L$62"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
                .MajorUnit = Target.Value
        Case "$L$63"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
                .MinorUnit = Target.Value
        Case "$M$60"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
                .MaximumScale = Target.Value
        Case "$M$61"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
                .MinimumScale = Target.Value
        Case "$M$62"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
                .MajorUnit = Target.Value
        Case "$M$63"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
                .MinorUnit = Target.Value
    
    'the above code is the original part that works perfectly
    'everything below was copied from above and modified to specify the secondary axis
    
        Case "$N$60"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, x1Secondary) _
                .MaximumScale = Target.Value
        Case "$N$61"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, xlSecondary) _
                .MinimumScale = Target.Value
        Case "$N$62"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, xlSecondary) _
                .MajorUnit = Target.Value
        Case "$N$63"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, xlSecondary) _
                .MinorUnit = Target.Value
        Case "$O$60"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
                .MaximumScale = Target.Value
        Case "$O$61"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
                .MinimumScale = Target.Value
        Case "$O$62"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
                .MajorUnit = Target.Value
        Case "$O$63"
            ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
                .MinorUnit = Target.Value
        Case Else
    End Select
    End Sub
    Last edited by Jacobs; 08-11-2007 at 10:54 AM.

  2. #2
    Registered User
    Join Date
    10-21-2003
    Posts
    2
    I figured it out. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1