Adjusting Chart Minimum and Maximum values via VBA

frabulator

Board Regular
Joined
Jun 27, 2014
Messages
108
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

frabulator

Board Regular
Joined
Jun 27, 2014
Messages
108
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.
 

frabulator

Board Regular
Joined
Jun 27, 2014
Messages
108
Re: Help with adjusting Chart Minimum and Maximum vales via VBA

Bump
 

Repush

Board Regular
Joined
Sep 21, 2015
Messages
133
Re: Help with adjusting Chart Minimum and Maximum vales via VBA

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

frabulator

Board Regular
Joined
Jun 27, 2014
Messages
108
Re: Help with adjusting Chart Minimum and Maximum vales via VBA

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:

Watch MrExcel Video

Forum statistics

Threads
1,101,914
Messages
5,483,689
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top