VBA read min & max values per sheet range to set primary and secondary axis chart

[bastian18]

Hello
I have an Access Db to export a query and create a chart based in worksheet ranges (B2 to last value down) and (C2 to last value down).
My command button my code creates a chart with primary and secondary axes values.
This is my code working well:

Sub cmdTransfer_Click()
Dim sExcelWB As String
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object


Set xl = CreateObject("excel.application")
'sExcelWB = "D:\testing2\"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")
'Set ch = xl.Charts.Add
Set ch = ws.Shapes.AddChart.Chart


With ch
    .ChartType = xlColumnClustered
    .SeriesCollection(2).AxisGroup = 2
    .SeriesCollection(2).ChartType = xlLineMarkers
    .ChartGroups(1).GapWidth = 69
    'Chart Title
    .HasTitle = True
    .ChartTitle.Text = "Plot" 
    .Axes(xlValue).MajorGridlines.Delete
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
    .SetElement (msoElementLegendBottom)
End with


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -207
ActiveSheet.Shapes("Chart 1").IncrementTop -237.75
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.4708333333, msoFalse, _
        msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.48090296, msoFalse, _
        msoScaleFromTopLeft


xl.Visible = True
xl.UserControl = True


End Sub

My problem here is, I do not know how to tell VBA to read min & max values per range to set each axis values.
I have been googling for hours and hours with no success.


Please. Does anyone know of a workaround to this?


Thanks in advance

 

[Micron]

About all I can offer at this point is something to try incorporating into your code:

With cht
  .axes(xlCategory,xlPrimary).MinimumScale = your 1st variable here
  .axes(xlCategory,xlPrimary).MaximumScale =  your 2nd variable here
  .axes(xlCategory,xlSecondary).MinimumScale =  your 3rd variable here
  .axes(xlCategory,xlSecondary).MaximumScale =  your 4th variable here
...

here's a link to the Excel object model for this and future reference. If you Google something like "excel vba chart axes scale" you'll get lots of results from those asking the same basic question as you.
NOTE: you might be required to dig deeper into the object model as I don't know if I chose the correct first parameter in the axes properties (xlCategory) for your purpose. Again, since you want to control 2 minimums and 2 maximums, you cannot use 2 variables the way you did. You could do away with the variables altogether if you simply use something like

.axes(xlCategory,xlPrimary).MinimumScale = DMax("yourQueryField","yourDomain")

and make sure you use the DMax function 4 times, getting the min and max from one field for one axis and the min and max from a different field for the other axis. Obviously, you substitute your query field name and query name in the above.
Hope it helps.

 

[bastian18]

Micron.
I already found the way to fix my error. This is the code solution from:https://www.excelforum.com/excel-pr...-setting-chart-axis-values-2.html#post4969801

[COLOR=#333333]With ch[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">    .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
 </code>[COLOR=#333333]End With[/COLOR]

[FONT=q_serif]I appreciate your time.[/FONT]