CODEWITHSUNDEEP
excelvbachartsaxis
Sebastian Salazar
Sebastian Salazar

Reputation: 23

VBA Runtime Error 1004 setting min and max chart axis values

I have an MS Acces Db. I have a command button to export (TransferSpreadsheet acExport) a query to Excel and create a Chart (Set ch = ws.Shapes.AddChart.Chart)

This is my code working fine:

With ch
  .ChartType = xlColumnClustered
  .SeriesCollection(2).AxisGroup = 2
  .SeriesCollection(2).ChartType = xlLineMarkers
  .ChartGroups(1).GapWidth = 69
  .Axes(xlValue).MajorGridlines.Delete
  .Axes(xlCategory, xlPrimary).HasTitle = False
  .Axes(xlValue, xlPrimary).HasTitle = False
  .SetElement (msoElementLegendBottom)
End with

I need to set a chart's axis maximum and minimum values using vba. I need to refer to chart already in the worksheet.

Any time I add to my code:

.MaximumScale = ActiveSheet.Range("Axis_max").Value
.MinimumScale = ActiveSheet.Range("Axis_min").Value

VBA displays ERROR 1004 “Application-defined or Object” I'm confused why my code does not run those instructions

I really would be grateful if someone could give me a steer on this,

All the best

Upvotes: 0

Views: 1270

Answers (2)

Sebastian Salazar
Sebastian Salazar

Reputation: 23

Finally, I found a code to set minimum and maximum vertical chart axes.

With ch
.ChartType = xlColumnClustered
.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers
.ChartGroups(1).GapWidth = 69

myMax = DMax("Total_Sal", "qry_task")
myMin = DMin("Total_Sal", "qry_task")

 With .Axes(xlvalue, xlPrimary)
        .MinimumScale = myMin
        .MaximumScale = myMax
 End With

myMax = DMax("Task_Val", "qry_task")
myMin = DMin("Task_Val", "qry_task")
With .Axes(xlvalue, xlSecondary)
        .MinimumScale = myMin
        .MaximumScale = myMax
    End With

End with

I really appreciate the initial help from ja72

To complete my code I had help from Andy Pope

Upvotes: 0

John Alexiou
John Alexiou

Reputation: 29244

Your problem is that .MinimumScale and .MaximumScale are not properties of a Chart but of an Axis objects.

Try instead:

.Axes(xlCategory, xlPrimary).MaximumScale = Sheet1.Range("Axis_max").Value
.Axes(xlCategory, xlPrimary).MinimumScale = Sheet1.Range("Axis_min").Value

Upvotes: 0

Related Questions