Adjusting Chart Minimum and Maximum values via VBA
Results 1 to 8 of 8

Thread: Adjusting Chart Minimum and Maximum values via VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Adjusting Chart Minimum and Maximum values via VBA

    I have been trying use a userform to adjust the minimum and maximum value scale on a chart. The problem is that my code keeps saying that "Method 'MinimumScale' of object 'Axis' not found" when trying to resize.

    How I am accessing the graph is by first having the user click on the graph (with the macro below):

    Code:
    Sub OpenZoomPan(control As IRibbonControl)
    
        Dim selectionType As String
        
          If TypeName(Selection) = "ChartArea" Or TypeName(Selection) = "PlotArea" Then
            Set chart_from_selection = Selection.Parent
            UserForm9.Show
          ElseIf TypeName(Selection) = "Series" Then
            Set chart_from_selection = Selection.Parent.Parent
            UserForm9.Show
          Else
            MsgBox ("Please select a chart to continue.")
          End If
    
        
    End Sub
    The above code works great. Then I adjust the scroll bar as needed and run the code below:

    Code:
    Sub ZoomAndPann()
    
        application.ScreenUpdating = False
    
    
        'X
        Dim mx As Double
        mx = UserForm9.Original_X_min - (UserForm9.Original_X_min * (UserForm9.ScrollBar3.Value / (UserForm9.ScrollBar3.Max / 2)))
        Dim xx As Double
        xx = UserForm9.Original_X_max - (UserForm9.Original_X_max * (UserForm9.ScrollBar3.Value / (UserForm9.ScrollBar3.Max / 2)))
    
        Dim AcSh As String
        AcSh = chart_from_selection.Name
        AcSh = Mid(AcSh, Len(ActiveSheet.Name) + 2, Len(AcSh) - (Len(ActiveSheet.Name) + 1))
        
        
        ActiveSheet.ChartObjects(AcSh).Activate
        
        'X
        ActiveChart.Axes(xlValue).MinimumScale = mx
        ActiveChart.Axes(xlValue).MaximumScale = xx
        
        'Y
        ActiveChart.Axes(xlCategory).MinimumScale = mx
        ActiveChart.Axes(xlCategory).Select
        ActiveChart.Axes(xlCategory).MaximumScale = xx
        
        
    
        application.ScreenUpdating = True
        
    End Sub
    The line that is acting up is 'ActiveChart.Axes(xlValue).MinimumScale = mx'. I know i have the chart selected because 'ActiveSheet.ChartObjects(AcSh).Activate' is firing and AcSh is collecting the name of the chart in question.

    Any help on this would be greatly appreciated. I'm sure it is something silly that I am over looking but I have been stuck on this for days.

    ~Frab

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    Update*

    I just tried to swap the xlValue code with the xlCategory code to see if it was an axis selection issue, turns out both of these axis give the same error.

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    Bump

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    Bump

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    Bump

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    bump

  7. #7
    Board Regular
    Join Date
    Sep 2015
    Location
    the Netherlands
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    no problem here . .
    what is the value of mx?

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with adjusting Chart Minimum and Maximum vales via VBA

    Quote Originally Posted by Repush View Post
    no problem here . .
    what is the value of mx?
    That is good to know that it is working on your end. That must mean there is something wrong with my macro/userform macro transfer or three way transfer from sheet to macro to userform.

    The variable in question is arbitrary. The value itself has been equated to '-3.5' for mx and '3.5' for xx. The reason why I say it doesn't matter is I have replace the place holders with actual values and still get the same result.
    Last edited by frabulator; Jul 16th, 2019 at 09:42 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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