VBA automatically change Min and Max Horizontal X Axis on Chart ? (code included)

[SereneSea]

Hello again VBA Neebie here,
I have another question now. I have a ganntt chart that I would like the x-axis to change with what is in cells H3 and I3. My minimum date start is in H3 and maximum is in I3.
If its possible to have the chart updated as soon as someone enters the dates?
I tried this code but I am getting an automation and unspecified error.

Sub ScaleXAxisWholeCycle()
    Dim Ch As Chart
    
    On Error Resume Next
    Set Ch = ActiveChart
    On Error GoTo 0
    
    If Not Ch Is Nothing Then
        With ActiveChart.Axes(xlCategory, xlPrimary)
            .MinimumScale = ActiveSheet.Range("H3").Value
            .MaximumScale = ActiveSheet.Range("I3").Value
        End With
    Else
        MsgBox "No Chart Found"
    End If
End Sub

 

[BSALV]

when you change the content of H3 or I3, but not as result of a formula !
If there is only one chart in that sheet, you can replace the "name of the chart" by 1

Private Sub Worksheet_Change(ByVal Target As Range)
     If Intersect(Target, Range("H3:I3")) Is Nothing Then Exit Sub
     With Me.ChartObjects("Name_of_the_chart").Chart            'or the name of the chart or its' indexnumber
          With .Axes(xlCategory, xlPrimary)
               .MinimumScale = Me.Range("H3").Value
               .MaximumScale = Me.Range("I3").Value
          End With
     End With
End Sub

 

[SereneSea]

Thank you so much for your reply, I probably am doing something wrong but I am getting the Run time error of "Method MinimumScale' of Object axis Failed.
My Chart is titled "Chart 9" and this is how I wrote the chart in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
     If Intersect(Target, Range("H3:I3")) Is Nothing Then Exit Sub
     With Me.ChartObjects("Chart 9").Chart            'or the name of the chart or its' indexnumber
          With .Axes(xlCategory, xlPrimary)
               .MinimumScale = Me.Range("H3").Value
               .MaximumScale = Me.Range("I3").Value
          End With
     End With
End Sub

 

[BSALV]

i used a variant of this macro in Price analysis.
But there it's the Y-axis and other cells that are used as trigger.

Normally i suspected an error in the line "with Me.chartobjects("Chart 9").chart", because of a no-matching name.
You suggest an error in the following line with "Minimumscale".
Which line is yellow and causes the error ?

If you replace H3 with a representative value like below, is the error solved ?

 .MinimumScale = 12345

 

[SereneSea]

i used a variant of this macro in Price analysis.
But there it's the Y-axis and other cells that are used as trigger.

Normally i suspected an error in the line "with Me.chartobjects("Chart 9").chart", because of a no-matching name.
You suggest an error in the following line with "Minimumscale".
Which line is yellow and causes the error ?

If you replace H3 with a representative value like below, is the error solved ?

 .MinimumScale = 12345

Thank you for your reply. I changed the minimum scale adnd i do not get an error anymore but also the macro does not do anything to my chart.

 

[BSALV]

use the macro-recorder, switch him on to record your modifications and select that chart, change manually the min and max value for that specific chart.
Stop the macro recorder, goto the recorded macro and change in that one the recorded min and max values to something else and rerun that macro.
Does the x-axis change now ?
If so,
- check now the name of that chart, is it "Chart 9" ? Normally that should match.
- now omit the "me." in " .MinimumScale = Me.Range("H3").Value" -> .MinimumScale = Range("H3").Value.
- change now the value of H3. You are sure that H3 and I3 are real values, hardcoded, not the result of a formula.
if not the change-event macro isn't triggered in the right way.
If H3 is a formula, what are the depending cells ?

This are all questions, first thoughts to know why it doesn't work.

 

[SereneSea]

the H3 and I3 are not formulas, just dates manually entered. I tried the macro recorder and do not see the "me." to omit. Below is the code that I got from recording :

Sub Macro4()
'
' Macro4 Macro
'

'
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 44593
    ActiveChart.Axes(xlValue).MaximumScale = 44926
End Sub

 

[SereneSea]

OO I was able to get it working by the code below, but how do I make it automatically, and not to click on a button.,
p.s. I changed my columns after so thats why it has different ranges.

Sub Datechange()

' Macro4 Macro
'
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = Range("I3").Value
    ActiveChart.Axes(xlValue).MaximumScale = Range("J3").Value
End Sub

 

[BSALV]

Great, so now it's working ?
Have a nice weekend.

 

[SereneSea]

It is working, but how do I make it to automatically change? Currently its only if I click on "run macro".