Error 424 when setting chart axis values

**gipsyfkls14** · 08-28-2018, 05:37 PM

Hello.
I have this code to export a query to excel and create a chart.
The code is working fine:

Set ch = ws.Shapes.AddChart.Chart
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

However, when I type beneat this code to set set maximun and minimum axis carth values

.Axes(xlCategory, xlPrimary).MaximumScale = Chart1.Range("Axis_max").Value
.Axes(xlCategory, xlPrimary).MaximumScale = Chart1.Range("Axis_max").Value

the following line always there is the Object required error '424':

.Axes(xlCategory, xlPrimary).MaximumScale = Chart1.Range("Axis_max").Value

I hope someone can help,

All the best

**6StringJazzer** · 08-28-2018, 08:27 PM

What is Chart1? Is that a worksheet, a chart sheet, or something else? Is "Axis_max" a named range in your file? Does it evaluate to a valid value in your worksheets?

(This is why we usually end up asking people to attach their Excel file.)

BTW you have repeated the exact same line of code twice. That will not cause your error, but does not appear to be what you intended.

**gipsyfkls14** · 08-28-2018, 11:36 PM

6StringJazzer

Many thanks for your reply.

Chart1 is the name of Chart assigned by the code
The Chart1 is in the Worksheet

I found this code from Shai Rado and I juste adapted it for me
https://stackoverflow.com/questions/...tal-x-axis-vba

my_max_var = 200
Set objCht = ActiveSheet.ChartObjects(1)

With object
    With .Chart.Axes(xlValue, xlPrimary)
        .MinimumScale = 1
        .MaximumScale = my_max_var
    End With

    ' modify minimum and maximum values of secondary axis
    With .Chart.Axes(xlValue, xlSecondary)
        .MinimumScale = 15000
        .CrossesAt = 20
        .MaximumScale = 3000000        

End With

However, I need VBA to read minimum and maximum values to set the axis values.
Can you tell me what is the meaning of instruction CrossesAt = 20?

I also attach a file with my qry_task sheet (with data) and my chart.
The file has a macro I recorded to format my chart.
I really appreciate if you can edit the macro code to be as a complete VBA code.

Many thanks in advance.

**gipsyfkls14** · 08-31-2018, 09:21 AM

Hello.

It's been over three days with no more replies.

I am still working this on my own

, but, I will still appreciate any help to fix my problem.

Thanks in advance!

**Andy Pope** · 08-31-2018, 09:45 AM

your original code fails because you are trying to set the category axis. change xlCategory to xlValue and it should work.

**gipsyfkls14** · 08-31-2018, 12:44 PM

Andy Pope. Again I appreciate your help.

I tested your suggestion within "With ch" in my code (below) and again I failed: "ERROR 424 and object is required" in line:

.Axes(xlValue, xlValue).MaximumScale = Chart 1.Range("Axis_max").Value

I think I don't know how to reference it.

Let me know about my project.

In Access I have a Form with 2 textboxes and one command button.
In txttask_plot, user types plot value (numeric)
In txttask_from, the user selects day1 (Date)
In txttask_to, the user selects day1 (Date)
The command button runs a code that works fine exporting the query and creating the Chart

This is my code in my command button:

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

Set xl = CreateObject("excel.application")
'This is the Path to export query data and Chart
sExcelWB = "D:\testing2\" & Replace(Me.txttask_from, "/", "_") & " - " & Replace(Me.txttask_to, "/", "_") & " - " & Replace(Me.txttask_plot, "/", "_") & "_qry_task.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")

Set ch = ws.Shapes.AddChart.Chart

With ch
    .ChartType = xlColumnClustered
    .SeriesCollection(2).AxisGroup = 2
    .SeriesCollection(2).ChartType = xlLineMarkers
    .ChartGroups(1).GapWidth = 69
    'Add Chart Title
    .HasTitle = True
    .ChartTitle.Text = "Plot" & ":" & Me.txttask_plot.Value & " " & Range("C1").Value & " " & vbCrLf & "Between" & " " & "(" & Me.txttask_from.Value & " - " &          Me.txttask_to.Value & ")"
    .Axes(xlValue).MajorGridlines.Delete
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
    .SetElement (msoElementLegendBottom)

End With
xl.Visible = True
xl.UserControl = True

End Sub

What I'm looking for is for VBA to execute the following:
Read range B2(to the last row) in the query minimum and maximum values to set values range (or escale?) in the primary vertical axis
Read range C2(to the last row) in the query minimum and maximum values to set values range (or escale?) in the secondary vertical axis
Therefore, any time I export a query to Excel VBA will set the minimum and maximum values in Primary and Secondary Axes values

I have been googling for the last 3 days

to find a solution with no success.

I really appreciate your help.

Cheers

**gipsyfkls14** · 08-31-2018, 10:21 AM

Andy. I really appreciate your quick reply.
I'm going to test your suggestion and I'll let you know about it.
Cheers

**Andy Pope** · 09-01-2018, 04:22 AM

Attached example creates chart and sets axes

If you are running this from Access I assume you are early binding excel in order for the excel constants to have correct values.
If you are using late binding then you will need to assign values to enumerate items such as

xlValue, xlCategory, xlPrimary, msoElementLegendBottom

**gipsyfkls14** · 09-01-2018, 02:43 PM

Andy Pope. Many thanks.
I am running my code from MS Access.

This is my code form MS Access:

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 myMax As Long
Dim myMin As Long


Set xl = CreateObject("excel.application")

sExcelWB = "D:\testing2\" & Replace(Me.txttask_from, "/", "_") & " - " & Replace(Me.txttask_to, "/", "_") & " - " & Replace(Me.txttask_plot, "/", "_") & "_qry_task.xls"

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

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")

Set ch = ws.Shapes.AddChart.Chart

xl.Visible = True
xl.UserControl = True

End Sub

However, I have been unable to set minimun and maximun values in primary and secondary axes in my chart and I am still working.

On the other hand, the excel file attached is very useful and educational to create graphics.
I appreciate your valuable help.

**gipsyfkls14** · 09-03-2018, 07:30 PM

Andy Pope.
Since 01/09/2018 I am studying how to use early binding and late binding.
In MS Acess VBA Editor I checked Microsoft Excel 16.0 Object Library
In MS Excel VBA Editor I checked Microsoft Access 16.0 Object Library

For the late binding I found the information on this links:
https://www.devhut.net/2012/04/19/ms...ords-to-excel/
http://www.granite.ab.ca/access/latebinding.htm
http://www.dyalog.com/uploads/confer...ndexamples.htm

I am a newbie on this respect.

Now my code exports to excel the query and creates the chart with:
Primary and secondary axis values
Labels values in the axis
Chart Background Colour

The code I used is almost the same in MS Excel.
So far, I could not find the instructions to set the minimum and maximum values for the primary axis and for the secondary axis.

In Excel, your code works well to set the minimum and maximum values of the primary axis and the secondary axis.
My problem is that I have not been able to adjust your lines of code to Access VBA.

Please, could you tell me which website I can visit to find the solution to my problem?
Cheers

**Andy Pope** · 09-04-2018, 03:26 AM

Not sure what to tell you.

The latest code you posted does not include any code to set the chart's axes.
If you are using late binding then as I said previously you will need to define variables to act in place of the excel constants which are available when early binding.

**gipsyfkls14** · 09-04-2018, 12:40 PM

Andy Pope. I really appreciate your reply.
The below code has lines ('Set maximum and minimum axis values) to set minimum and maximum axis values from the query.

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 myMax As Double
Dim myMin As Double

'On Error GoTo err_handler

Set xl = CreateObject("excel.application")
sExcelWB = "D:\testing2\" & "qry_task.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")

Set ch = ws.Shapes.AddChart.Chart

'Set maximun and minimun axis values:confused:
myMax = DMax("Total_Sal", "qry_task")
myMin = DMin("Total_Sal", "qry_task")
myMax = DMax("Task_Val", "qry_task")
myMin = DMin("Task_Val", "qry_task")

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

xl.Visible = True
xl.UserControl = True
End Sub

However, in the chart:

Primary axis minimum value starts at 0.
Primary axis maximum value is not query value.
Secondary axis minimum value starts at 0.
Secondary axis maximum value is not query value.

Many thanks in advanced

**Andy Pope** · 09-05-2018, 03:51 AM

Again your code does not incorporate the code I provided to set those properties, so no surprise it not doing anything.

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 myMax As Double
Dim myMin As Double
Dim xlvalue As Long
Dim xlcategory As Long
Dim xlPrimary As Long
Dim xlSecondary As Long
Dim xlLineMarkers As Long

' If you are using late binding then you need to load these variables with values.
xlvalue = 2
xlcategory = 1
xlPrimary = 1
xlSecondary = 2
xlLineMarkers = 65

'On Error GoTo err_handler

Set xl = CreateObject("excel.application")
sExcelWB = "D:\testing2\" & "qry_task.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")

Set ch = ws.Shapes.AddChart.Chart

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 .SeriesCollection(1)
        With .Axes(xlvalue, xlPrimary)
            .MinimumScale = myMin
            .MaximumScale = myMax
        End With
    End With
    
    myMax = DMax("Task_Val", "qry_task")
    myMin = DMin("Task_Val", "qry_task")
    With .SeriesCollection(2)
        With .Axes(xlvalue, xlSecondary)
            .MinimumScale = myMin
            .MaximumScale = myMax
        End With
    End With

End With

xl.Visible = True
xl.UserControl = True
End Sub

**gipsyfkls14** · 09-05-2018, 01:13 PM

Andy Pope.
I understood that your code is for Excel. Your code works excellent in Excel.
I really apologize for omitting your code in my previous posts.

I have been trying to write your VBA Excel code within my code in MS Access VBA

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 cht As Chart
Dim data As Range, dataValuesPrimary As Range, dataValuesSecondary As Range, dataLabels As Range

Set xl = CreateObject("excel.application")
sExcelWB = "D:\testing2\" & "qry_task.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_task", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_task")

'Set ch = ws.Shapes.AddChart.Chart
Set cht = ActiveSheet.Shapes.AddChart.Chart
       
    With cht
        .ChartType = xlLineMarkers
            With .SeriesCollection.NewSeries
                 .Name = "='" & ActiveSheet.Name & "'!" & dataValuesPrimary.Cells(1, 1).Offset(-1, 0).Address
                 .Values = dataValuesPrimary
                 .XValues = dataLabels
            End With
            
            With .SeriesCollection.NewSeries
                 .Name = "='" & ActiveSheet.Name & "'!" & dataValuesSecondary.Cells(1, 1).Offset(-1, 0).Address
                 .Values = dataValuesSecondary
                 .XValues = dataLabels
                 .AxisGroup = 2
            End With
               
            With .Axes(xlValue, xlPrimary)
                 .MinimumScale = Application.WorksheetFunction.Min(dataValuesPrimary)
                 .MaximumScale = Application.WorksheetFunction.Max(dataValuesPrimary)
            End With

            With .Axes(xlValue, xlSecondary)
                 .MinimumScale = Application.WorksheetFunction.Min(dataValuesSecondary)
                 .MaximumScale = Application.WorksheetFunction.Max(dataValuesSecondary)
            End With
    End With

xl.Visible = True
xl.UserControl = True

End Sub

But any time I click th command dbutton VBA coloured in blue the underlined words:
.MinimumScale = Application.WorksheetFunction.Min(dataValuesPrimary)
and displays this message: "MS Access VBA Compilation Error Method or data member not found."

I used Dim ch As Object ''Excel.Chart instead of Dim cht As Chart and VBA displays same error
I used Dim cht As Chart instead of Dim ch As Object ''Excel.Chartand VBA displays same error

I have been googling on how to fix such error with no success.

Again I really appreciate your help.
Cheers

**Andy Pope** · 09-06-2018, 03:26 AM

The application refers to the application the code is being executed within, which in your case is Access and not Excel.
If you where to use the Excel worksheet functions you would need to reference the excel object

.MinimumScale = xl.WorksheetFunction.Min(dataValuesPrimary)

But in your previous code you were determining min and max with access functions so I don't think you need to use that part of excel at all

I will say it one more time, if you are late binding then you need to ensure the excel enumeration you use has the correct values.

**gipsyfkls14** · 09-06-2018, 10:41 AM

Andy Pope.
Thank you for your time and effort in helping me.

I tested you excel code in excel and works perfectly.
I'm worried because I have been unable to run in Access the code you provided.

The attached image shows in yellow 'Error, 438 Object doesn't support this property or method.' error438_code_export_qry_chart_primary_secondary_axis.jpg. I can provide the code if needed.

Although I have been studying about early binding and late binding I still do not know which code lines indicate if it is early binding or late binding.
I am completely stumped on a VBA setting vertical axis min and max values.:confused.

Cheers

**Andy Pope** · 09-06-2018, 11:05 AM

Early binding means you have included a reference to the library of the object, in your case the excel application. This is done within VBE using Tools > References.
If you have not done that then you are using late binding.

You code fails because you have used nested With syntax where the objects being referenced are not nested,
,Axes is not an object of the .seriescollection object.

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

**gipsyfkls14** · 09-06-2018, 01:54 PM

Andy Pope.

Those two lines of code have kept me with a headache for a week

Now my code runs.

Thank you very much for your help.

Error 424 when setting chart axis values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Re: Error 424 when setting chart axis values

Thread Information

Users Browsing this Thread

Similar Threads

Setting Y axis scale on XY chart where X axis only shows partial Series

[SOLVED] First time setting up a chart with dynamic axis....and I stink at it

Setting Chart Y axis to start at no less than 0

Dynamic Setting for Chart Axis Minimum

Runtime Error 1004 when Setting Setting Series Values

Setting a Minimum X-axis Value to Stacked Bar Chart

[SOLVED] Error when setting chart Axis value

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions