0

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Sebastian Salazar
  • 23
  • 2
  • 8
  • Can you verify that `ActiveSheet.Range("Axis_max").Value` returns a numeric value? Wouldn't rather pull the value from a _specific_ sheet, like `Sheets("chart_options").Range("Axis_max").Value` – John Alexiou Aug 27 '18 at 17:07
  • ja72 I appreciate your reply. When testing your recommendation when I type: ActiveSheet.Range("Axis_max") VBA displays ERROR 1004 and VBA paints yellow the line of code On the other hand, when I type Sheets("chart_options").Range("Axis_max").Value VBA displays Error 9 and VBA paints yellow the line of code – Sebastian Salazar Aug 27 '18 at 22:03
  • Well, you don't have a sheet named "chart_options". I just made up that name as a placeholder for where you would store the axis scaling information. You need to use whatever sheet name you have these values stored at. – John Alexiou Aug 28 '18 at 12:42
  • Ja72 When I type instead of ("chart_options"), "Sheet1" VBA displays Error 1004 When I type instead of ("chart_options"), "qryname" VBA displays Error 1004 Thanks for your help. – Sebastian Salazar Aug 28 '18 at 14:38
  • If this is Access VBA and not Excel VBA, then functions like `ActiveSheet()` or `Sheets()` won't work. Access does not know anything about spreadsheets. You need a reference to the created Excel workbook to make this work. – John Alexiou Aug 30 '18 at 16:37
  • I know Access VBA does not know something about spreadsheets. Unfortunately, I still don't know how to deal with the reference you indicate. Please. Could you give me a light? – Sebastian Salazar Aug 30 '18 at 17:06

2 Answers2

0

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
John Alexiou
  • 28,472
  • 11
  • 77
  • 133
  • ja72 I was unable to run your code. For hours I have been googling an I found this code here from Shai Rado and I just adapted for me and works fine. However, with this code, I have to type minimum and maximum values. I need VBA to read those values from Sheet1 (range B2 and range C2) to set the axis chart values also in Sheet1. Shai Rado link:https://stackoverflow.com/questions/41414615/maximum-scale-of-secondary-horizontal-x-axis-vba – Sebastian Salazar Aug 30 '18 at 01:20
  • So is the problem that you cannot set the scale, or that you cannot read a value. The linked code is identical to my answer, except that instead of hard-coding a value, it is read from `Sheet1`. – John Alexiou Aug 30 '18 at 12:19
  • I cannot set the scale with the code. I think my problem is that I don't know the code I have to type before: .Axes(xlCategory, xlPrimary).MaximumScale = Sheet1.Range("Axis_max").Value .Axes(xlCategory, xlPrimary).MinimumScale = Sheet1.Range("Axis_min").Value – Sebastian Salazar Aug 30 '18 at 13:49
  • If I record a Macro to insert a chart in the worksheet, how can I use these lines of code: .MaximumScale = ActiveSheet.Range("Axis_max").Value .MinimumScale = ActiveSheet.Range("Axis_min").Value I have been googling with no success. I really appreciate your help. – Sebastian Salazar Aug 30 '18 at 16:20
0

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

Sebastian Salazar
  • 23
  • 2
  • 8